Property Scorecard Workbook: Airtable Webhook → Templated Google Sheet → Apps Script Score Sync

Problem:

Producing a property's financial scorecard meant manually copying a spreadsheet template, pasting in utility and maintenance data, running calculations by hand, and typing the resulting scores back into Airtable for every single property.

Solution:

Built a Make.com scenario triggered by webhook. It fetches the property from Airtable, spins up a Google Sheets workbook from a master template, and writes the folder/file IDs back to both the sheet and the Airtable record. A Router then runs three branches: one pulls Utility Bills from Airtable, searches the matching sheet rows, and updates them; one clears the maintenance range, searches Maintenance Values in Airtable, sets the row counter variable, and repopulates the rows; the third sleeps, fires a Google Apps Script to run the workbook's calculation engine, sleeps again, then reads back nine computed cells, CE, MR, IQ, VE, and TP scores plus Annual OpEx, Capex 12mo, Capex 10yr, and Vendor Total and writes them onto the property's Airtable record.

Result:

A fully populated, calculation-complete property workbook with nine synced performance scores is now generated in one webhook call instead of a multi-hour manual spreadsheet exercise.