Update:
I created a field for "7-D Ad Spend" and one for "7-D Leads" in my main "Google Ads" base.
Next I created a script that runs twice for each of those fields (4 in total):
- On entry creation
- On entry update
This is the script:
let config = input.config();
if (!config.tableName || !config.dateField || !config.inputField || !config.resultField || !config.numDays) {
console.error("Error: Please provide all required configuration parameters.");
return;
}
let table = base.getTable(config.tableName);
let query = await table.selectRecordsAsync();
for (let record of query.records) {
let date = record.getCellValue(config.dateField);
let valueInput = record.getCellValue(config.inputField);
if (date !== null && valueInput !== null) {
let dateRangeStart = new Date(date);
let dateRangeEnd = new Date(date);
dateRangeStart.setDate(dateRangeStart.getDate() - (config.numDays - 1)); // Subtracting (n - 1) to include the current day
let sortedRecords = query.records.slice().sort((a, b) => {
return new Date(a.getCellValue(config.dateField)) - new Date(b.getCellValue(config.dateField));
});
let lastNRecords = sortedRecords.filter(r => {
let recordDate = new Date(r.getCellValue(config.dateField));
return recordDate >= dateRangeStart && recordDate <= dateRangeEnd;
});
let sum = lastNRecords.reduce((acc, r) => {
let value = r.getCellValue(config.inputField);
return acc + (value !== null ? value : 0);
}, 0);
await table.updateRecordAsync(record, {
[config.resultField]: sum
});
console.log(sum);
}
}
The script uses the input config. So, I needed to setup the fields used in the script. Those are:
- tableName (The table inside the base your field lives in. In this case "Google Ads")
- dateField (The field you store your date value in. Here it is "date")
- inputField (The value you want to get the sum of. This is either "Ad Spend" or "Leads" in this case)
- numDays (Can be any number. Here I am using "7" since I want to get the sum of the last 7 days)
- resultField (The field that you want to use as output. Mine is called "7-D CPL")
You don't need to change anything inside of the code itself. The only thing you need to adjust is the automation for each field. Keep in mind you need them twice per field (once for creating and once for updating the field).
Here you can see the setup in my automation:
Last step was to create a third field in my main "Google Ads" base called "7-D CPL". This is a formula field that divides the two values.
IF(
AND({7-D Ad Spend}, {7-D Leads}),
{7-D Ad Spend} / {7-D Leads},
BLANK()
)
This formula then calculates the moving average (7 days) for our CPL value.
After you set this up, you can hide the fields used for calculating the sum (7-D Ad Spend) and (7-D Leads) since they are only needed for calculation.
