How to batch row data and send a single JSON payload?

Question

I currently use a Google Apps Script on a Google Sheet, that sends individual row data to AWS API Gateway to generate a screenshot. At the moment, multiple single JSON payload requests are causing some Lambda function failures. So I want to batch the row data and then send as a single payload, so a single AWS Lambda function can then perform and complete multiple screenshots.

How can I batch the JSON payload after iterating the data on each line in the code below?

function S3payload () {
  var PAYLOAD_SENT = "S3 SCREENSHOT DATA SENT";
  
  var sheet = SpreadsheetApp.getActiveSheet(); // Use data from the active sheet
  
  // Add temporary column header for Payload Status new column entries
  sheet.getRange('E1').activate();
  sheet.getCurrentCell().setValue('payload status');
  
  var startRow = 2;                            // First row of data to process
  var numRows = sheet.getLastRow() - 1;        // Number of rows to process
  var lastColumn = sheet.getLastColumn();      // Last column
  var dataRange = sheet.getRange(startRow, 1, numRows, lastColumn) // Fetch the data range of the active sheet
  var data = dataRange.getValues();            // Fetch values for each row in the range
  
  // Work through each row in the spreadsheet
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];  
    // Assign each row a variable   
    var index = row[0];     // Col A: Index Sequence Number
    var img = row[1];   // Col B: Image Row
    var url = row[2];      // Col C: URL Row
    var payloadStatus = row[lastColumn - 1];  // Col E: Payload Status (has the payload been sent)
  
    var siteOwner = "[email protected]";
    
    // Prevent from sending payload duplicates
    if (payloadStatus !== PAYLOAD_SENT) {  
        
      /* Forward the Contact Form submission to the owner of the site
      var emailAddress = siteOwner; 
      var subject = "New contact form submission: " + name;
      var message = message;*/
      
      //Send payload body to AWS API GATEWAY
      //var sheetid = SpreadsheetApp.getActiveSpreadsheet().getId(); // get the actual id
      //var companyname = SpreadsheetApp.getActiveSpreadsheet().getName(); // get the name of the sheet (companyname)
      
      var payload = {
        "img": img,
        "url": url
      };
      
      var url = 'https://requestbin.herokuapp.com/vbxpsavc';
      var options = {
        'method': 'post',
        'payload': JSON.stringify(payload)
      };
      
      var response = UrlFetchApp.fetch(url,options);
      
      sheet.getRange(startRow + i, lastColumn).setValue(PAYLOAD_SENT); // Update the last column with "PAYLOAD_SENT"
      SpreadsheetApp.flush(); // Make sure the last cell is updated right away
      
      // Remove temporary column header for Payload Status    
      sheet.getRange('E1').activate();
      sheet.getCurrentCell().clear({contentsOnly: true, skipFilteredRows: true});
      
    }
  }
}

Example individual JSON payload

{"img":"https://s3screenshotbucket.s3.amazonaws.com/realitymine.com.png","url":"https://realitymine.com"}

Example desired output result

[
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/gavurin.com.png","url":"https://gavurin.com"},
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/google.com.png","url":"https://google.com"},
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/amazon.com","url":"https://www.amazon.com"},  
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/stackoverflow.com","url":"https://stackoverflow.com"},
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/duckduckgo.com","url":"https://duckduckgo.com"},
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/docs.aws.amazon.com","url":"https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-features.html"},  
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/github.com","url":"https://github.com"},  
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/github.com/shelfio/chrome-aws-lambda-layer","url":"https://github.com/shelfio/chrome-aws-lambda-layer"},  
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/gwww.youtube.com","url":"https://www.youtube.com"},   
    {"img":"https://s3screenshotbucket-useast1v5.s3.amazonaws.com/w3docs.com","url":"https://www.w3docs.com"}       
]

Answer 1

Modifications

1. Do not call UrlFetchApp methods in a loop unless no other way. Although Google offers generous quota, it is not unlimited, and you will quickly burn through it on any substantial amount of rows and send frequency.
2. Use modern ES6 features like map to convert rows of values into objects in the format of the desired payload. Note that you will have to enable V8 runtime to use them.

What follows is a runnable test snippet showcasing how you could have modified your script. I opted to exclude status update logic from it, as it is up to you to decide how to update the status in case of batch update failure:

//TEST MOCKS:
const SpreadsheetApp = {
  getActiveSheet() {
  
    const Sheet = {
      getLastRow() { return 3; },
      getLastColumn() { return 5; },
      getDataRange() {
      
        const Range = {
          getValues() {
            return new Array(Sheet.getLastRow())
            .fill([])
            .map(
              (r,ri) => new Array(Sheet.getLastColumn())
              .fill(`mock row ${ri}`)
              .map((c,ci) => `${c} cell ${ci}`)
            );
          }
        };
        
        return Range;
      }
    };
    
    return Sheet;
  }
};

const UrlFetchApp = {
  fetch(uri, options) {
    console.log({ uri, options });
  }
};
//END MOCKS;

const sendToS3 = () => {

  var PAYLOAD_SENT = "S3 SCREENSHOT DATA SENT";

  var sheet = SpreadsheetApp.getActiveSheet();

  var startRow = 2;
  var numRows = sheet.getLastRow() - 1;
  var lastColumn = sheet.getLastColumn();

  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  var siteOwner = "[email protected]";

  const appURI = 'https://hfcrequestbin.herokuapp.com/vbxpsavb';

  const payloads = data.map(([index, img, url]) => ({ img, url }));

  const options = {
    'method': 'post',
    'payload': JSON.stringify(payloads)
  };

  const response = UrlFetchApp.fetch(appURI, options);
}

sendToS3();

---

Notes

1. When batching POST payloads, keep in mind that there is a quota on maximum body size per request (currently 50 MB).
2. Do not call I/O (input/output) methods such as getRange, getValue in a loop, they are slow by nature, use batch methods like getDataRange, getValues, setValues, etc and perform all modifications on in-memory arrays only.
3. Use activate methods only when you explicitly want to change the focus, do not rely on it to determine a range. Just use normal references to cells obtained through methods like getRange.

Answer 2

Try sending the data as a list/Array. And on the server side iterate over the list/Array.

eg:

{
    "payload": [{
        "img": "https://s3screenshotbucket.s3.amazonaws.com/website1.com.png",
        "url": "https://website1.com"
    }, {
        "img": "https://s3screenshotbucket.s3.amazonaws.com/website2.com.png",
        "url": "https://website2.com"
    }]
}