In my attempt to iterate through the nested "line_items" element in a JSON response triggered by a post event, I aim to populate a Google Sheets spreadsheet using Google Apps Script. My objective is to write the elements within each "line_item" into new rows as shown in the image below:
https://i.sstatic.net/RDCit.png
{
"purchaseorder": {
"date": "2019-11-27",
"submitted_date": "",
"delivery_address": {
"zip": "91240"
},
...
...
}
}
The code provided here is the result of my efforts and although it doesn't perform as expected, I've managed to successfully write JSON data to a spreadsheet using a different approach before.
// Function to handle POST request to the webapp
function doPost(e) {
// Exit if no data is received
if (e == undefined) {
console.log("no data");
return HtmlService.createHtmlOutput("need data");
}
// Get the active spreadsheet and specify the target sheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('Sheet3');
// Parse the JSON data from the POST request
var event = JSON.parse(e.postData.contents);
var data = event.purchaseorder;
// Iterate through the data and populate the rows array
var rows = [];
var items;
for (var i = 0; i < data.line_items.length; i++) {
items = data.line_items[i];
rows.push([items.name, items.quantity, items.rate]);
}
// Write the data to the specified sheet
dataRange = sheet.getRange(1, 1, rows.length, 3);
dataRange.setValues(rows);
}
Any assistance in debugging and improving this code to achieve the desired outcome will be highly appreciated.