variable range in Google Script

Question

Is there any simple method to make one side of the range a variable?

function myFunction() {


var ss = SpreadsheetApp.openById("1RXJ9uR9oDV0gEL7ZyLtSi-WqFxk5Pg-7oitViHZOyf8");
 SpreadsheetApp.setActiveSpreadsheet(ss);
 var sheet = ss.getSheets()[0];

//This logs the value in the very last cell of this sheet

 var lastRow = sheet.getLastRow();
 var lastColumn = sheet.getLastColumn();
 var lastCell = sheet.getRange(lastRow, lastColumn);

// add values in the last cells and calc the sum

 var cellTotal = sheet.getRange(lastRow + 1, lastColumn - 1);
 var cellTotalVal = sheet.getRange(lastRow + 1, lastColumn);
 cellTotal.setValue('total').setFontWeight("bold");

 //here I try a numorous options to get my range right 
 cellTotalVal.setFormula("=SUM(D1:CONCATENATE('D', lastRow))");

};

What I want to achieve here is a SUM where the first value is D1 and the last is a var named lastCell.

Thank you for any suggestions

Karl_S · Accepted Answer · 2017-01-11 15:03:21Z

1

You can leave the range open:

=SUM(D1:D)

will get you the entire column.

=SUM(D5:D)

will get you from D5 to the last row.

EDIT

Based on your comment below, this is what you are looking to do:

cellTotalVal.setFormula("=SUM(D1:D" + lastRow + ")");

This will create a string where assuming lastRow = 100, the string is "=SUM(D1:D100)"

edited Jan 11, 2017 at 15:03

answered Jan 11, 2017 at 14:37

Karl_S

3,5922 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kuba04 Over a year ago

Yeah, that one I found elsewhere before. I am asking this because it's more like a learning exercise for me. I am new to GS and curious if it is possible at all to use a variable in ranges or sums because I couldn't find it in Google Ref.

Karl_S Over a year ago

Google Apps Script is based on JavaScript, so the syntax applies. For generic items such as this, also search for JavaScript answers. See my Edit above for my understanding of your comment, @Kuba88

kuba04 Over a year ago

Thanks Karl! I wasted the whole afternoon searching for different ways of doing it, now I can move on. Your code solves the issue. I didn't know where to put the space when concatenating...

Karl_S Over a year ago

You are welcome, @Kuba88 The Concatenate() portion is not necessary since your script is doing that for you. If just for the exercise, you could include it as well, as I am guessing you may have figured out.

Collectives™ on Stack Overflow

variable range in Google Script

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related