"for" loop in Google Script introducing a TypeError

Question

I've created (actually, modified) a script to export a CSV file for a specific range within a Google Sheet. It is working well:

function convertRangeToCsvFile_(csvFileName,delimiter) {
  // Get the selected range in the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var ws = sheet.getRange("F3:G501");

  try {
    var data = ws.getValues();
    var csvFile = undefined;

    // Loop through the data in the range and build a string with the CSV data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(delimiter) != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // Join each row's columns
        // Add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(delimiter) + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

However, I now want to set it up so that the script iterates over three different ranges of cells and concatenates them all into one big long CSV. I'm trying to do this using a for loop, however when I do so the script returns an error:

TypeError: Cannot find function getValues in object 0.

From what I can tell, this might be some kind of scope issue, but I can't for the life of me find anything that helps me solve it. Here is my broken code:

function convertRangeToCsvFile_(csvFileName,delimiter) {
  // Get the selected range in the spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var ranges = [sheet.getRange("F3:G501"),sheet.getRange("H3:I501"),sheet.getRange("J3:K501")];

  try {

    for (var ws in ranges) {

      var data = ws.getValues();
      var csvFile = undefined;

      // Loop through the data in the range and build a string with the CSV data
      if (data.length > 1) {
        var csv = "";
        for (var row = 0; row < data.length; row++) {
          for (var col = 0; col < data[row].length; col++) {
            if (data[row][col].toString().indexOf(delimiter) != -1) {
              data[row][col] = "\"" + data[row][col] + "\"";
            }
          }

          // Join each row's columns
          // Add a carriage return to end of each row, except for the last one
          if (row < data.length-1) {
            csv += data[row].join(delimiter) + "\r\n";
          }
          else {
            csv += data[row];
          }
        }
        csvFile = csv;
      }
    }  

    return csvFile;

  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

I've tried adjusting where the for loop starts and ends (originally it did not take in the var data = ws.getValues(); line, which of course caused an "undefined" error.)

But despite my best efforts, I can't find any information out there that addresses this question in a way that my (very inexperienced) brain can parse!

Any input is much appreciated.

JSDBroughton · Accepted Answer · 2016-07-02 16:47:35Z

1

You are looping ranges as an object and not an array therefore the first key you find is 0

Loop with an incremental for loop instead:

var ws;
for (var w =0; w < ranges.length; w += 1) {
    ws = ranges[w];

The rest I haven't checked but if you've changed nothing else should be fine.

answered Jul 2, 2016 at 16:47

JSDBroughton

4,0644 gold badges35 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

"for" loop in Google Script introducing a TypeError

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related