Quicken google apps script so I can return success response within Shopify 5 second limit

Question

I have this google apps script I wrote that I'm using as a web app as an endpoint for a Shopify webhook.

The issue I'm having is that Shopify has a 5 second limit to receive a success response otherwise the webhook will fire again to ensure you don't miss it.

The problem is my script takes too long to finish triggering a duplicate webhook which runs my code multiple times which I don't want.

Is there a way to respond quicker or clean up my script to finish quicker?

PLEASE NOTE: I need my script to be easily modified since exact values might change or be different in final version as I'm still developing this app. (additionally I need a way that if a value is missing it will leave that column blank, hence not mixing up value with column headers)

function doPost(e){
  var data = JSON.parse(e.postData.contents);
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
 
  var l = data.line_items.length; 
 
  for (var i=0;i<l;i++){
  var prop = data.line_items[i].properties;


  if (prop.length>0){
  var pdf = prop.find(function(x) {if(x.name == "_pdf") return x});
  if (!pdf){pdf = "Prop not found";}else{pdf = pdf.value};
  
  var shape = prop.find(function(x) {if(x.name.toLowerCase() == "shape") return x});
  if (!shape){shape = "Prop not found";}else{shape = shape.value};
  
  var test = prop.find(function(x) {if(x.name == "test") return x});
  if (!test){test = "Prop not found";}else{test = test.value};

  }else{
  var pdf = "N/A"
  var shape = "N/A"
  var test = "N/A"
  };


  var count = "Item "+ (i+1) + " of " + l;
  var qty = data.line_items[i].quantity;
  var title = data.line_items[i].title;
  var id = data.id.toString();
  var email = data.email;
  var totalPrice = data.total_price;
  var discounts = data.total_discounts;
  var acceptAds = data.buyer_accepts_marketing;
  var orderStatus = data.order_status_url;
  var addr = data.shipping_address.address1;
  var city = data.shipping_address.city;
  var state = data.shipping_address.province;
  var zip = data.shipping_address.zip;
  var phone = data.shipping_address.phone;
  var firstName = data.shipping_address.first_name;
  var lastName = data.shipping_address.last_name;
  var orderNum = data.name;
  var d = new Date(data.created_at).toLocaleString();
  ss.appendRow([d,orderNum,email,count,title,shape,test,qty,totalPrice,discounts,pdf,firstName,lastName,addr,city,state,zip,phone,orderStatus]);
  
if (pdf != "N/A"){
if (pdf != "Prop not found"){
  var res = UrlFetchApp.fetch(pdf);
  var blob = res.getBlob();
  var createFile = DriveApp.getFolderById('xxxxxxxxxxxxx').createFile(blob.getAs('application/pdf'));
  var fileName = orderNum + " " + qty;
  createFile.setName(fileName);
}}
  };
}

question from:https://stackoverflow.com/questions/65617637/quicken-google-apps-script-so-i-can-return-success-response-within-shopify-5-sec

Answer 1

It's slower than using the PropertiesService, but I like using Sheets as a queue. (I use this with services that require responses within 3 seconds.) Not only is it easier to work with, but I've actually had issues with using Properties that are addressed with the appendRow() method:

Appends a row to the spreadsheet. This operation is atomic; it prevents issues where a user asks for the last row, and then writes to that row, and an intervening mutation occurs between getting the last row and writing to it.

When you receive the POST data, simply add it to the queue and terminate. Apps Script will send a 200 success response, so Shopify shouldn't send duplicate requests.

Then have a time-driven trigger that runs a processQueue() function at the interval of your choice.

function doPost(e) {
  const queue = new Queue(SpreadsheetApp.getActive().getId(), "Unprocessed", "Processed");
  queue.append(e.postData.contents, skipRefresh = true);
}

function processQueue() {
  const queue = new Queue(SpreadsheetApp.getActive().getId(), "Unprocessed", "Processed");
  while (queue.hasNext()) {
    try {
      const data = JSON.parse(queue.next());
      
      doSomethingWithShopifyData(data); // Process your data
      
      queue.moveToProcessed();
    } catch (error) {
      console.error(error);
      queue.skip();
    }
  }
}

function doSomethingWithShopifyData(data) { /* your existing code, but with appropriate modifications */ }

Here's the class I use to abstract the spreadsheet into a queue. I have it setup to preserve all of the data moving it from an unprocessed to a processed sheet. You may prefer to simply delete the data once processed.

/**
 * A spreadsheet is used as a makeshift queue for processing requests asynchronously.
 * @param {string} spreadsheetId - The ID of the spreadsheet to be used for the queue.
 * @param {string} unprocessedSheetName - The name of the sheet to be used for storing unprocessed items.
 * @param {string} processedSheetName - The name of the sheet to be used for storing processed items.
 */
class Queue {
  constructor(spreadsheetId, unprocessedSheetName, processedSheetName) {
    this.index = 0;
    this.row = 1;
    this.spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    this.unprocessedSheet = this.spreadsheet.getSheetByName(unprocessedSheetName);
    this.processedSheet = this.spreadsheet.getSheetByName(processedSheetName);
  }
  
  /**
   * Determines whether calling next() will return an item.
   * @returns {boolean}
   */
  hasNext() {
    if (this.unprocessedValues == null) { this.refreshUnprocessedValues(); }
    return this.index < this.unprocessedValues.length;
  }
  
  /**
   * Get and save the unprocessed element values to the queue.
   * @returns {object[]}
   */
  refreshUnprocessedValues() {
    try {
      const range =this.unprocessedSheet.getRange(1, 1, this.unprocessedSheet.getLastRow());
      this.unprocessedValues = range.getValues();
    } catch (error) {
      this.unprocessedValues = [];
    }
    return this.unprocessedValues;
  }
  
  /**
   * Get the next element from the queue.
   * @returns {string}
   */
  next() {
    return this.unprocessedValues[this.index++][0];
  }
  
  /**
   * Skip the current queue element. Update row property to maintain synchronization
   * with the spreadsheet range.
   */
  skip() {
    this.row++;
  }
  
  /**
   * Add new data to the queue for processing.
   * @param {string} data - The data to add to the queue.
   * @param {boolean} [skipRefresh] - Default: false. If true, will skip refreshing the queue values.
   */
  append(data, skipRefresh) {
    this.unprocessedSheet.appendRow([data]);
    if (!skipRefresh) { this.refreshUnprocessedValues(); }
  }
  
  /**
   * Move a payload out of the unprocessed sheet and into the processed sheet. Uses the payload
   * at the top of the unprocessed range.
   */
  moveToProcessed() {
    const cell = this.unprocessedSheet.getRange(this.row, 1);
    
    // Move into processed sheet
    this.processedSheet.appendRow([cell.getValue()]);
    
    // Move out of unprocessed sheet
    cell.deleteCells(SpreadsheetApp.Dimension.ROWS);
  }
}