function namedRanges() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for(var i=3; i<504; i++) {
var r = ss.getRange('A3:Q3'+i);
ss.setNamedRange('Song'+i, r);
}
}
The above formula, is not stepping thru the 500 song rows in my spreadsheet and naming each row, "Song 3" for row A3-Q3, etc. (starting at row 3 as I have headers in rows 1 and 2) as I expected.
So what I am trying to do is create named ranges for all 500 songs in the sheet.
What I am expecting:
Name - Range
Song3 - Songs!A3:Q3
Song4 - Songs!A4:Q4
Song5 - Songs!A5:Q5
etc.
What I am getting:
Song3 - Songs!A3:Q33
Song4 - Songs!A3:Q34
Song5 - Songs!A3:Q35
etc.
I have spent two days trying to track this down by searching (in vain). I'm sure it's easy. Anybody know how to do this?
Below is a "new and better" version of the script I am using (which also incorporates @Casper 's answer). It is "new" and "better" because it counts the number of columns in the sheet, rather than taking a static value which makes it more flexible as it will name a range the width of the sheet, no matter that width (as measured by columns).
function namedRanges() {
var ss = SpreadsheetApp.openByUrl("url");
var sheet = ss.getActiveSheet();
//first column = 1
var numcols = sheet.getLastColumn()
Logger.log(numcols);
for(var i=101; i<501; i++) {
var r = sheet.getRange(i,1,1,numcols);//row, column, num rows, num cols
ss.setNamedRange('Song'+i, r);
}
}
You're looking for something like this I suppose:
function namedRanges() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for(var i=3; i<504; i++) {
var r = ss.getRange('A'+i+':Q'+i);
ss.setNamedRange('Song'+i, r);
}
}
Your original code was appending the row number to your static text:
var r = ss.getRange('A3:Q3'+i);
Basically reads as:
var r = ss.getRange('A3:Q3'+3);
The +
sign concatenates Q3
and 3
resulting in Q33
. However, in my code you define the columns as static text and the row numbers as variable.
var r = ss.getRange('A'+i+':Q'+i);
Therefore reads as (i=3):
var r = ss.getRange('A'+3+':Q'+3);
Resulting in A3:Q3
Hope that helps.