Skip to main content

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:





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

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.recordsdi - 1];


    let previousValue = previousRecord.getCellValue(field);


    let currentRecord = query.recordsdi];


    let currentValue = currentRecord.getCellValue(field);


    await table.updateRecordAsync(currentRecord, {


        '% change': currentValue / previousValue - 1,


    });


}

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.recordsdi - 1];


    let previousValue = previousRecord.getCellValue(field);


    let currentRecord = query.recordsdi];


    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

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.recordsdi - 1];


    let previousValue = previousRecord.getCellValue(field);


    let currentRecord = query.recordsdi];


    let currentValue = currentRecord.getCellValue(field);


    await table.updateRecordAsync(currentRecord, {


        '% change': currentValue / previousValue - 1,


    });


}

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.

Reply


Terms & Conditions