Search an element from a parsed JSON string array in google apps script

As mentioned in the comments, the input is definitely not JSON, and trying to convert it looks pretty tough. I think regex would be the easier way. Here's a sample that worked with your input:

function doPost(e) {

  var notJsonString = e.postData.contents;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data");
  var timeStamp = new Date();
  var time = Utilities.formatDate(timeStamp, "BST", "dd/MM/yyyy, h:mm a");
  var nextRow = sheet.getLastRow()+1;

  //Insert the data into the sheet  
  sheet.getRange(nextRow, 1).setValue(time);
  sheet.getRange(nextRow, 4).setValue(extractAfter("amount=", notJsonString));
  sheet.getRange(nextRow, 5).setValue(extractAfter("reference=", notJsonString));
  sheet.getRange(nextRow, 6).setValue(extractAfter("is_supplementary=", notJsonString));
}

function extractAfter(str, source){
  var regex= new RegExp(`(?<=(${str}))[^,}\s]*`)
  return regex.exec(source)[0]
}

Here's the result after sending a POST to the WebApp:

The regex (?<=(your_string))[^,}\s]* is a lookbehind and it matches any string after the your_string pattern and before a ,, a } or a whitespace. Based on the JSON-like format of your input this should get the right value no matter where it is generated, but you may run into issues if you want to get a value that does include one of the excluded characters.

As a bonus, your original code was failing at the JSON parse since the input could not be parsed to JSON, but this code would have worked if it had been valid:

function doPost(e) {

  var jsonString = e.postData.contents;
  var event = JSON.parse(jsonString)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Data");
  var timeStamp = new Date();
  var time = Utilities.formatDate(timeStamp, "BST", "dd/MM/yyyy, h:mm a");
  var lastRow = sheet.getLastRow();

  //Insert the data into the sheet  
  sheet.getRange(lastRow + 1, 1).setValue(time);
  sheet.getRange(lastRow + 1, 4).setValue(event.amount);
  sheet.getRange(lastRow + 1, 5).setValue(event.reference);
  sheet.getRange(lastRow + 1, 6).setValue(event.metadata.is_supplementary);
}

And this is what valid JSON would have looked like:

{"processing":{"acquirer_reference_number":24022122407531018095, "acquirer_transaction_id":57631018089}, "id":"pay_n4m745lnx7uy2tmgx4z26mksi", "action_id":"act_qqno6t6jctdkzbuk2fu3js71e", "response_summary":"Approved", "metadata":{"is_supplementary":"True", "ps_id":9, "token":"tok_war7hee5nole7d21bknt45dm", "transaction":7125, "hash":"84c478fdfbdb2515d46542adbd6ggd516dc0048fd67", "order_id":83245142, "sandbox":0}, "amount":271.0, "processed_on":"2022-09-21T13:35:32Z", "response_code":10000, "currency":"USD", "reference":2694931}

And formatted:

{
   "processing":{
      "acquirer_reference_number":24022122407531018095,
      "acquirer_transaction_id":57631018089
   },
   "id":"pay_n4m745lnx7uy2tmgx4z26mksi",
   "action_id":"act_qqno6t6jctdkzbuk2fu3js71e",
   "response_summary":"Approved",
   "metadata":{
      "is_supplementary":"True",
      "ps_id":9,
      "token":"tok_war7hee5nole7d21bknt45dm",
      "transaction":7125,
      "hash":"84c478fdfbdb2515d46542adbd6ggd516dc0048fd67",
      "order_id":83245142,
      "sandbox":0
   },
   "amount":271.0,
   "processed_on":"2022-09-21T13:35:32Z",
   "response_code":10000,
   "currency":"USD",
   "reference":2694931
}

Daniel

I've modified the GAS now, and subsequently, I am retrieving the post in JSON now, see below, but I still cannot collect an element from it. I would like to get a value in column 4, but dot notation doesn't appear to be working.

function doPost(e) {
  var jsonString = e.postData.getDataAsString();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Import");
  var timeStamp = new Date();
  var time = Utilities.formatDate(timeStamp, "GMT", "dd/MM/yyyy, h:mm a");
  var nextRow = sheet.getLastRow()+1;

  //Insert the data into the sheet  
  sheet.getRange(nextRow, 1).setValue(time);
  sheet.getRange(nextRow, 3).setValue(jsonString);
  sheet.getRange(nextRow, 4).setValue(jsonString.amount);
}

This is the post data received in JSON format:

{"id":"evt_l4wuy3s85ieqko2jyd5ndrakfa","type":"payment_captured","created_on":"2022-09-23T09:44:15Z","data":{"action_id":"act_vcmb5fho27l2d7yf54i7uggggy","response_code":"10000","response_summary":"Approved","amount":2980,"metadata":{"sandbox":"0","hash":"3dd6w92a99eaf5ef045e5f1d702b73dg0983b060","card_token":"tok_vd65f37ss5sejf5lshlbiaklve","ps_id":"9","order_id":"55300847","transaction":"6720","is_supplementary":"False"},"processing":{"acquirer_transaction_id":"87543695702","acquirer_reference_number":"24023212166085436959085"},"id":"pay_a47jsqd5gj6kxerj33xjwarwai","currency":"GBP","processed_on":"2022-09-23T09:44:15Z","reference":"2698535"},"_links":{"self":{"href":"https://api.paygateway.com/events/evt_k4wuy3sf6ieejo2jye5jdrak6a"},"payment":{"href":"https://api.paygateway.com/payments/pay_a4cjsqs6la6kxerj33xjws2wai"}}}

This is the final Google Apps Script that is bound to a sheet and published as a web app which acts as a webhook to collect data.

This works well and collects the data from the JSON payload. I can access elements from the JSON payload and set the value in a specific cell in a Google Sheet.

I was previously using Zapier to process this as a webhook, but the sheer volume of tasks was getting excessive, so using this method should be better and cheaper!

I hope someone finds this useful in the future.

function doPost(e) {

  var jsonString = e.postData.getDataAsString();
  var event = JSON.parse(jsonString)
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Import");
  var timeStamp = new Date();
  var time = Utilities.formatDate(timeStamp, "GMT+1", "dd/MM/yyyy, h:mm a");
  var nextRow = sheet.getLastRow()+1;

  //Insert the data into the sheet  
  sheet.getRange(nextRow, 1).setValue(time);
  sheet.getRange(nextRow, 3).setValue(jsonString);
  sheet.getRange(nextRow, 4).setValue(event.data.amount);
  sheet.getRange(nextRow, 5).setValue(event.data.metadata.is_supplementary);
  sheet.getRange(nextRow, 6).setValue(event.data.reference);
}