Search code examples
google-apps-scriptgoogle-sheetsnamed-ranges

What is the proper syntax for concatenating text and variables in SpreadsheetApp functions?


    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?


Solution

  • This works:

    function namedRanges() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      for(var i=100; i<601; i++) {
        var r = ss.getRange(('A'+i+':Q'+i));
        ss.setNamedRange('Song'+i, r);
      }
    }