I have this Google Apps Script bound to a sheet and published as a web app which is acting as a webhook to collect data. This works well and collects the first two results in the script (Insert the data into the sheet) just fine. Time and the complete data package into columns 1 and 3 respectively.
function doPost(e) {
var jsonString = e.postData.getDataAsString();
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, 3).setValue(event["data"]);
sheet.getRange(lastRow 1, 6).setValue(data.reference);
}
The information that is retrieved from the webhook in column 3 is in this format once parsed but I'm struggling to Search for an element in this parsed JSON string array using google apps script and was hoping someone can point me in the right direction.
{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}
I'd really like to search for these three elements within the data and return them into columns 4, 5 and 6 respectively. The order in which these appear seems to be random, so I cannot rely on an index:
- amount=271.0
- reference=2694931
- is_supplementary=True
You can see I've attempted to use dot notation and I've also tried bracket notation but to no avail.
Thanks for reading.
CodePudding user response：
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
}
CodePudding user response：
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"}}}
CodePudding user response：
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);
}