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()

Answer 2

I'd propose to make three functions: main(), get_all_users(), update_users() and run the latter function in the loop this way:

function main() {  

  var all_users = get_all_users();

  for (var i = 0; i < all_users.length; i += 100) {
    var users = all_users.slice(i, i + 100);
    update_users(users);
  }
}

function get_all_users() {
  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;
  });
  return users;
}

function update_users(users) {
  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());
}

It's always a good idea to keep the main function as short and clear as it's possible. And break the algo into the separate functions where every function does exactly one relatively simply thing: get users from the sheet, send request, etc.

Answer 3

You can try this for-loop method that iterates every 100:

function updateManyUsers() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var data = sheet.getDataRange().getDisplayValues();
  var headers = data.shift(); //remove headers to the array and assign it to headers variable
  for (var i = 0; i < data.length; i += 100){
    var tempArr = data.slice(i, i+100)
    var users = tempArr.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": users }),
      'contentType': 'application/json',
      'muteHttpExceptions': true
    };
    var response = UrlFetchApp.fetch(url, options);
  }  
}

Sample data:

......

variable users content on each iteration:

Also, upon checking the documentation, when using Update Many Users batch update the data format should look like this:

{
  "users": [
    { "id": 10071, "name": "New Name", "organization_id": 1 },
    { "id": 12307, "verified": true }
  ]
}