#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 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@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.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"}

enter image description here

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"}       
]


Get this bounty!!!

Leave a Reply

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