#StackBounty: #javascript #json #google-apps-script #google-sheets #aws-lambda How to batch row data and send a single JSON payload?

Bounty: 100

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 sheet = SpreadsheetApp.getActiveSheet(); // Use data from the active sheet
  // Add temporary column header for Payload Status new column entries
  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@example.com";
    // 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://hfcrequestbin.herokuapp.com/vbxpsavb';
      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.getCurrentCell().clear({contentsOnly: true, skipFilteredRows: true});

Example individual JSON payload


enter image description here

Example desired output result


Get this bounty!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.