0
352
ViewsPopulating a google sheet using apps script after receiving data from Postman
So I am trying to fill a google sheet using apps script. I am supposed to receive a JSON object from Postman through POST. The object will appear as such:
{
"email":"jdoe@gmail.com",
"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,
}
We are assuming that it will always be in this format and order without fail. The data is then modified and put into this sheet: Google Sheet
I would like help on how to populate the sheet with just the data from the JSON object first as I am failing at that. I believe I can modify it for my purpose from there. I would aslo like to know how I can convert the timestamps into days of weeks and do subtraction on them as well. I am new to both Apps Script and Postman so any help would be appreciated.
Thank You.
This is what I have so far:
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);
}
1 answers
Answer question0
If you want to keep the headers as is, add a line before your current headers with the exact spelling, i.e. : full_name, email, score, max_score, attempt_starttime, attempt_endtime, invited_on, percentage_score and correct your json (fix commas situation) ...
For instance
function myFunction() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Sheet1");
const dataHeaders = ws.getRange(1, 1, 1, ws.getLastColumn()).getValues()[0];
const body = `{
"email":"jdoe@gmail.com",
"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
}`
const jsonBody = JSON.parse(body);
const data = dataHeaders.map(h => jsonBody[h]);
ws.appendRow(data);
}
works fine
