DEV Community

lucanu
lucanu

Posted on

Auto‑Generate Minimalist Finance Sheets with the Free Google Sheets API

#javascript #google #finance #automation

The average Italian millennial spends 45 % of their net salary on recurring subscriptions, yet 67 % of them never track those expenses in a spreadsheet. ## 1. Set up a Google Cloud project and enable the Sheets API

  1. Open https://console.cloud.google.com/ and create a new project called budget‑templates. 2. Navigate to APIs & Services → Library, search for Google Sheets API, and click Enable. 3. So in Credentials, create an OAuth 2.0 Client ID (application type: Desktop app). Download the JSON file and rename it credentials.json. Actionable takeaway: You now have a service account that can read and write any spreadsheet you own – the foundation for automated template generation. ## 2. Scaffold a minimalist template with Node.js

Install the official client library:

npm install googleapis@latest
Enter fullscreen mode Exit fullscreen mode

Create generate-template.js:

const fs = require('fs');
const {google} = require('googleapis');
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];
const creds = JSON.parse(fs.readFileSync('credentials.json'));

async function auth() {
 const {client_secret, client_id, redirect_uris} = creds.installed;
 const oAuth2Client = new google.auth.OAuth2(client_id, client_secret, redirect_uris[0]);
 const authUrl = oAuth2Client.generateAuthUrl({access_type: 'offline', scope: SCOPES});
 console.log('Open this URL and paste the code:', authUrl);
 const code = await new Promise(r => {
 process.stdin.once('data', d => r(d.toString().trim()));
 });
 const {tokens} = await oAuth2Client.getToken(code);
 oAuth2Client.setCredentials(tokens);
 return oAuth2Client;
}

async function createSheet(auth) {
 const sheets = google.sheets({version: 'v4', auth});
 const resource = {properties: {title: 'Minimal Budget 2024'}};
 const {data: {spreadsheetId}} = await sheets.spreadsheets.create({resource});
 const requests = [
 {updateSheetProperties: {properties: {sheetId: 0, title: 'Income'}, fields: 'title'}},
 {addSheet: {properties: {title: 'Expenses'}}},
 {addSheet: {properties: {title: 'Summary'}}}
 ];
 await sheets.spreadsheets.batchUpdate({spreadsheetId, requestBody: {requests}});

 // Populate headers
 const values = [
 ['Date', 'Source', 'Amount €'],
 ['Date', 'Category', 'Amount €']
 ];
 await sheets.spreadsheets.values.update({
 spreadsheetId,
 range: 'Income!A1:C1',
 valueInputOption: 'RAW',
 requestBody: {values: [values[0]]}
 });
 await sheets.spreadsheets.values.update({
 spreadsheetId,
 range: 'Expenses!A1:C1',
 valueInputOption: 'RAW',
 requestBody: {values: [values[1]]}
 });
 console.log('Template created:', `https://docs.google.com/spreadsheets/d/${spreadsheetId}`);
}

auth().then(createSheet).catch(console.error);
Enter fullscreen mode Exit fullscreen mode

Run node generate-template.js, follow the OAuth prompt, and you will receive a fresh spreadsheet with three sheets ready for data entry. Actionable takeaway: Execute the script once and you have a reusable, zero‑cost budgeting workbook that matches the minimalist style preferred by EU users. ## 3. Add dynamic monthly roll‑overs with Apps Script

Open the newly created spreadsheet, click Extensions → Apps Script, and paste:

function rollOver() {
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const lastMonth = Utilities.formatDate(new Date(new Date().setMonth(new Date().getMonth()-1)), 'CET', 'MMMM yyyy');
 const newSheet = ss.insertSheet(lastMonth);
 const source = ss.getSheetByName('Summary');
 const data = source.getDataRange().getValues();
 newSheet.getRange(1,1,data.length,data[0].length).setValues(data);
}
Enter fullscreen mode Exit fullscreen mode

Save and set a trigger (Triggers → Add Trigger) to run rollOver on the first day of each month. Actionable takeaway: Your budget automatically archives the previous month’s summary, keeping the workbook tidy without manual copying. ## 4. Pull real‑time exchange rates for Euro‑based travelers

Install the free exchangerate.host API via fetch. Add this function to the same Apps Script project:

function importRate(currency) {
 const url = `https://api.exchangerate.host/latest?base=EUR&symbols=${currency}`;
 const response = UrlFetchApp.fetch(url);
 const rate = JSON.parse(response.getContentText()).rates[currency];
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary');
 sheet.getRange('E2').setValue(rate);
}
Enter fullscreen mode Exit fullscreen mode

Run importRate('GBP') to write the current EUR→GBP rate into cell E2. Reference this cell in expense conversions, e.g., =C5*$E$2. Actionable takeaway: Exchange‑rate look‑ups cost nothing and stay up‑to‑date, a practical benefit for freelancers earning in multiple currencies. ## 5. Share, lock, and protect the template for collaborators

  1. Click Share, set Anyone with the link → Viewer. 2. In File → Protect sheet, protect the Summary tab, allowing only the owner to edit formulas. 3. Distribute the view‑only link to roommates or partners; they can fill Income and Expenses without breaking calculations. Actionable takeaway: A single shared link turns the spreadsheet into a collaborative budgeting hub while preserving the integrity of totals. Recommendation for today: Clone the generate-template.js script, replace the project name with your own, run it, and immediately start logging your August income and expenses. No subscription, no hidden fees – just a functional budget sheet ready for EU tax year 2024.

If this was useful: I packaged a deeper version as LinkedIn Outreach Sequence Prompt Pack.

This article was drafted with AI assistance.

Top comments (0)