Google Script - Auto-summing non-adjacent numbers
As I previously posted, I'm working with a program in our organization that is looking to make their invoicing process more efficient. After getting it so the invoice numbers were being automatically generated, we next wanted to auto-sum the individual item totals for a grand total.
The challenges - 1. the first value wasn't until the middle of the spreadsheet, and 2. the values were not adjacent in the spreadsheet. Luckily for us, they were an equidistant number of cells from each other (otherwise I think this script would have been a lot more complicated!). The following script will work for a similar situation (all in grey - including the final } bracket):
The formula in the brackets is summing those values I indicated I wanted for i, starting with the sum value I sent earlier. Since I am looking across the row I use [0][i-1].
Finally, I am assigning this number to my established last cell. That's it for the script. You will need to set up a trigger for it to run - since my data is coming from a form, my trigger is on form submit.
It would be fairly easy to modify this so it is summing values in columns as well. The key to using it is that the values wanted are the same number of cells apart.
The challenges - 1. the first value wasn't until the middle of the spreadsheet, and 2. the values were not adjacent in the spreadsheet. Luckily for us, they were an equidistant number of cells from each other (otherwise I think this script would have been a lot more complicated!). The following script will work for a similar situation (all in grey - including the final } bracket):
function totalDue() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var lastCell = sheet.getRange(lastRow, lastColumn-4);
var values = sheet.getRange(lastRow, 11, 1, lastColumn-18).getValues();
var sum = 0;
for (var i=1;i < lastColumn-18;i=i+6)
{
sum=sum+values[0][i-1];
}
lastCell.setValue(sum);
}
The top 2 variables are the standard for getting the data from the first sheet on the spreadsheet the script is bound to. Change the zero in [0] to get a different sheet (they are in numerical order starting with 0).
The next three variables are getting the numbers of rows and columns, and getting the cell ID of the last cell in the bottom right of the range. I use lastColumn-4 as I am running an add-on that adds information to the 4 columns after my data, so this will exclude that information.
The next variable is the important one -
var values = sheet.getRange(lastRow, 11, 1, lastColumn-18).getValues();
This one is going to get the values from my data. It is getting it based on the following range - these are what you need to adjust to match your data.
The next three variables are getting the numbers of rows and columns, and getting the cell ID of the last cell in the bottom right of the range. I use lastColumn-4 as I am running an add-on that adds information to the 4 columns after my data, so this will exclude that information.
The next variable is the important one -
var values = sheet.getRange(lastRow, 11, 1, lastColumn-18).getValues();
This one is going to get the values from my data. It is getting it based on the following range - these are what you need to adjust to match your data.
- lastRow - it is starting with the last row of data (I'm only adding numbers from a single row entry)
- 11 - it is starting with the 11th column of data; for me, this is the first value that needs to be summed
- 1 - it is only grabbing data from 1 row
- lastColumn-18 - My total number of columns, subtracting the first 11 and the last 7 (all of which I don't need/want to include)
The next important line - one important note: there is no need for spaces before and after the greater than sign. I had to include them for it to show up on the webpage.
for (var i=1;i < lastColumn-18;i=i+6)
I'm indicating that for variable i,
- starting with the first cell I indicated (the last row and 11th column) part 1,
- I want the data from every 6th cell after that part 3
- until I've reached the last possible one before my indicated end call of data collected part 2.
Finally, I am assigning this number to my established last cell. That's it for the script. You will need to set up a trigger for it to run - since my data is coming from a form, my trigger is on form submit.
It would be fairly easy to modify this so it is summing values in columns as well. The key to using it is that the values wanted are the same number of cells apart.
