How do I use a script to translate a column into a number for a weekly update report?

[James_Brace]

I am using an automation to send a weekly update to the team on sales performance. Right now I have a basic automation that pulls all new records for the week, and puts it into an email as a grid. I am able to find the total number of unique sales but pulling the length of the grid, however, I cannot have sum the sales amount column.

Could I write a script that would take all the new records for the week and sums sales column? I was the script and I was able to pull in the column from previous step results, but SUM was note a supported function in the script editor, nor did it recognize my pulled value.

Any help?

Thanks
James

[Rose_K]

Hello and welcome to the Community @James_Brace!

James_Brace:

Could I write a script that would take all the new records for the week and sums sales column?

You could definitely write a script to do what you have outlined above, but if you do not have experience with JavaScript I think there may be an easier way to get the total sales value onto the table itself.

You could set the sales total on the table itself by associating the records together as described in this support article.

James_Brace:

I was the script and I was able to pull in the column from previous step results, but SUM was note a supported function in the script editor, nor did it recognize my pulled value.

If you would like to continue using your script you can add values within a script by iterating through your records and then using += to add them together like so:

let table = base.getTable('Employees');
let view = table.getView('Grid view');

let result = await view.selectRecordsAsync();
let runningTotal = 0;
// iterate through all the records
for (let record of result.records) {
    // using += will reassign the value of runningTotal to 
    // include the 'Amount' value from the record
    runningTotal += record.getCellValue('Amount');
    await table.updateRecordAsync(record, {
        'Running total': runningTotal,
    });
}

Let me know if I can help provide any more information, and if this helped answer your question please mark it as the solution :white_check_mark: .