Help

% Change calculation in Table using Script Block?

Topic Labels: Scripting extentions
2123 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Peter_Schindler
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello,

I’m wrestling with one thing that is very easy in Excel (or Google Sheets), but I haven’t be able to find how it can be implemented in Airtable.

I’ve created a Table. I add a record ever day to track the number of occurrences such as: Visitors to an establishment.

Date Day Visits % Change
23.3. Mo 27 NA
25.3. Tu 28 = (28/27)-1 (in formula in Excel (C2/C1) - 1
26.3. We 30 = (30/28)-1 (in formula in Excel (C3/C2) - 1
27.3 Th 35 = (35/30)-1 (in formula in Excel (C4/C3) - 1
etc

Where the % Change Column (or Field in Airtable speak) is, of course, calculated by a Formula.

The Excel version is here:

Screenshot 2020-03-26 at 19.14.02

Is it possible to achieve this with a Script Block? If yes, I will upgrade to Pro and try it.

Any help would be very much appreciated.

Peter

3 Replies 3
Stephen_Suen
Community Manager
Community Manager

Hey @Peter_Schindler:

This should be possible in the script block; I recommend remixing the Running total example. Instead of doing a cumulative sum, you could divide each cell value by the cell value in the previous record. Something like this (you’ll need to change the names of the table/view/field based on your data):

// change these names to pick a table, view, and field:
let table = base.getTable('Daily visitors');
let view = table.getView('Grid view');
let field = table.getField('Visits');

let query = await view.selectRecordsAsync();
for (let i = 1; i < query.records.length; i++) {
    let previousRecord = query.records[i - 1];
    let previousValue = previousRecord.getCellValue(field);
    let currentRecord = query.records[i];
    let currentValue = currentRecord.getCellValue(field);
    await table.updateRecordAsync(currentRecord, {
        '% change': currentValue / previousValue - 1,
    });
}

Hello Stephen,

thanks so much for answering not only “yes, can do”, but showing an example because it also illustrates more generally what Scripts in Airtable are capable of.

Much appreciate it!
Peter

I learned more about scripting in your comment than I have in the last month of looking up scripting stuff.

That was such a useful example I can’t even explain. Ha ha

You basically just empowered me to use any spreadsheet formula in Airtable. This rocks.