How Can I Create Batch In Javascript Loop To Select X Rows Then Next X Rows Until All Rows Are Done?

I have a google sheet with some data and I am trying to combine all cell data in a JSON variable so I can pass it on to API to do something. I have this javascript function that ta

Solution 1:

Try this:

functionrowsForAPI(){
  var ss = SpreadsheetApp.getActiveSheet();
  var data = ss.getDataRange().getValues(); // 2D array with all of the data in the sheet.var rowCount = ss.getLastRow(); // To know how many rows have datavar obj = []; // Array where the row objects will be stored var temp = 0; // A counter of how many rows have been processed. var results = []; // Array where the resulting JSON objects will be stored and returned.

  Logger.log(rowCount/200)
  for (var i = 0; i < (rowCount/200); i++){  
    for (var j  = temp; j < 200*(i+1); j++){
      obj.push(data[j]); // Push row into object.
      temp = j;
      if (temp == rowCount-1) // Got to the end of the data (if there are less than 200 rows in this batch).break;
    }
    temp++; // Update row count.
    results.push(JSON.stringify(obj)); // Adds the JSON object to an array
    obj = []; // Clear the array of the 200 rows stored before the next loop starts.
  }
  return results;
}

functiondoSomething(){
  var objects = rowsForAPI();
  var curr;
  for ( var i = 0; i < objects.length; i++){ // Go through each batch
    curr = objects[i]; // Current batch.// Do the API thing with curr...
  }
}

This method will return an array of JSON objects that holds batches of 200 rows from the Sheet, it will also stop if it reaches the end of the data in the sheet.