I'm currently working on populating a Google sheet using apps script. The task involves receiving a JSON object from Postman via POST request. The structure of the object is as follows:
{
"email":"<a href="/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="84eee0ebe1c4e3e9e5ede8aae7ebe9">[email protected]</a>",
"full_name": "John Doe",
"score": 4.0,
"max_score": 5.0
"attempt_starttime": "2021-09-21T03:28:13+0000",
"attempt_endtime":"2021-09-21T03:28:35+0000",
"invited_on":"2021-09-21T03:27:50+0000",
"percentage_score":100.0,
}
It's essential that the format and order of the data remain consistent. After modifying the data, it gets inserted into this specific Google Sheet: Google Sheet
I need assistance with initially populating the sheet with data solely from the JSON object, as I'm encountering challenges in doing so. Additionally, I'm looking to convert timestamps into days of the week and perform calculations on them. Given my limited experience with Apps Script and Postman, any guidance would be greatly appreciated.
Thank you.
This is what my current progress looks like:
function doPost(e) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Sheet1");
const headers = ws.getRange(1, 1, 1, ws.getLastColumn()).getValues()[0];
const dataHeaders = headers.slice()
const body = e.postData.contents;
const jsonBody = JSON.parse(body);
const data = dataHeaders.map(h => jsonBody[h]);
ws.appendRow(data);
}