Search code examples
google-apps-scriptgoogle-sheets

How do I increment a Google Sheets range via getRange?


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?

Improved version

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);
  }
}

Solution

  • 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.