Limiting Loop Range to 100 Rows at at time

Question

I have the following script that takes data from my Sheet and updates records via a POST API call; however there is a limit of 100 calls at a time so I'm looking for a way to add that to my script if possible. I also need to ensure that the header row (row1) is sent. So essentially the first loop is rows 1-101, second loop is row 1 and rows 102-201 etc. Not even sure this is possible

 function updateManyUsers() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var [headers, ...rows] = sheet.getDataRange().getDisplayValues();
  Logger.log([headers,rows]);
  var users = rows.map(r => {
    var temp = {};
    headers.forEach((h, j) => {
      if (r[j] != "") temp[h] = r[j];
    });
    return temp;
  });
  var url = 'https://redaccted.zendesk.com/api/v2/users/update_many.json';
  var user = 'morris.coyle@redacted_still/token';
 var pwd = 'Every_redacted';
  var options = {
    'method': 'PUT',
    'headers': {
      'Authorization': "Basic " + Utilities.base64Encode(user + ':' + pwd)
    },
    'payload': JSON.stringify({ users }),
    'contentType': 'application/json',
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

Thanks in advance.

Moz

Answer 1

Description

I have created a simple example of how to slice 100 rows from the data.

I have a simple data set of Header plut 256 rows of data. See screen shot.

Screen shots

Script

function updateManyUsers() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let values = sheet.getDataRange().getValues();
    console.log("rows = "+values.length);
    let headers = values.shift();
    let i = 0;
    let numUsers = 100;
    let j = numUsers;
    while( i < values.length ) {
      if( j < values.length ) {
        var users = [].concat(headers,values.slice(i,j));
      }
      else {
        var users = [].concat(headers,values.slice(i));
      }
      console.log("header = "+users[0][0]);
      console.log("users[1] = "+users[1][0]);
      console.log("users[99] = "+users[users.length-1][0]);
      i = i+numUsers;
      j = j+numUsers;
      // Now build your opsions
    }
  }
  catch(err) {
    console.log(err);
  }
}

1:18:04 PM  Notice  Execution started
1:18:05 PM  Info    rows = 257
1:18:05 PM  Info    header = Header
1:18:05 PM  Info    users[1] = 1
1:18:05 PM  Info    users[99] = 100
1:18:05 PM  Info    header = Header
1:18:05 PM  Info    users[1] = 101
1:18:05 PM  Info    users[99] = 200
1:18:05 PM  Info    header = Header
1:18:05 PM  Info    users[1] = 201
1:18:05 PM  Info    users[99] = 256
1:18:05 PM  Notice  Execution completed

Reference

• Array.shift()
• Array.concat()
• Array.slice()