Unable to add json data to Google sheets through HTTP post request using Google Apps Script
Objective:
To append multiple rows at a time into a google sheet by passing the data as JSON via HTTP post request.
Steps to produce the problem:
- Created a sheet named "test log" in google sheets.
- Wrote Apps Script to receive JSON data containing multiple records from a HTTP post request and append each reacord to a new row in the sheet. [Find code below]
- Ran cURL to make a HTTP post request to append the JSON data to the sheet. [Find code below]
Result:
- Apps Script raises the following error.
Sorry, unable to open the file at this time. Please check the address and try again.
- An empty row is added to the sheet after the request is finished.
Things to note:
The apps script is deployed as a web-app with access to any annonymus user.
Things I have tried (None of them worked):
- Changed the App Script owner from myself to annonymus. To check if the issue was related to google account.
- Opened an ingognito window in my browser, then opened the google sheet and ran the cURL.
- In the post request, removed multiple records from the JSON data and replaced it with one record only.
- Tried solutions posted to similar question like this on stackoverflow and else where.
Google Apps-Script Code:
// Google Apps Script Code
var sheetName = "test log"; // Replace with your sheet name
function doGet(e) {
return ContentService.createTextOutput("GET request processed successfully").setMimeType(ContentService.MimeType.TEXT);
}
function doPost(e) {
// Your code for handling POST requests
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
// Get JSON payload from the request
var jsonData = e.postData.contents;
var data = JSON.parse(jsonData);
// Map incoming data to corresponding headers
var mappedData = {
"Date": data.date || "",
"Energy Consumed (kWh)": data.energyConsumed || "",
"Meter ID": data.slaveID || "",
"Gateway Poll Result": data.gatewayPollResult || "",
"1st Register Value": data.reg1val || "",
"2nd Register Value": data.reg2val || ""
// Add more mappings as needed
};
// Construct the data array with the correct order
var orderedData = orderData(mappedData, ["Date", "Energy Consumed (kWh)", "Meter ID", "Gateway Poll Result", "1st Register Value", "2nd Register Value"]);
// Insert data at the second row (index 1)
sheet.insertRowBefore(2);
sheet.getRange(2, 1, 1, orderedData.length).setValues([orderedData]);
return ContentService.createTextOutput("Data added successfully").setMimeType(ContentService.MimeType.TEXT);
}
// Function to order the data array based on specified headers
function orderData(data, headers) {
var orderedData = [];
headers.forEach(function (header) {
if (data.hasOwnProperty(header)) {
orderedData.push(data[header]);
} else {
orderedData.push(""); // If the header doesn't have corresponding data, insert an empty string
}
});
return orderedData;
}
appsscript.json:
{
"timeZone": "Asia/Kolkata",
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"webapp": {
"executeAs": "USER_DEPLOYING",
"access": "ANYONE_ANONYMOUS"
}
}
cURL:
curl -X POST
-H 'Content-Type: application/json'
-d '[{"Date":"1705599163","Energy Consumed (kWh)":"0.00","Meter ID":"1","Gateway Poll Result":"0","1st Register Value":"0","2nd Register Value":"0"},{"Date":"1705599163","Energy Consumed (kWh)":"999259.13","Meter ID":"2","Gateway Poll Result":"9","1st Register Value":"18803","2nd Register Value":"62898"},{"Date":"1705599163","Energy Consumed (kWh)":"4603.56","Meter ID":"3","Gateway Poll Result":"9","1st Register Value":"32081","2nd Register Value":"19084"},{"Date":"1705599163","Energy Consumed (kWh)":"4563.96","Meter ID":"4","Gateway Poll Result":"9","1st Register Value":"18416","2nd Register Value":"19083"},{"Date":"1705599163","Energy Consumed (kWh)":"42749.73","Meter ID":"5","Gateway Poll Result":"9","1st Register Value":"18214","2nd Register Value":"64956"},{"Date":"1705599163","Energy Consumed (kWh)":"31587.45","Meter ID":"6","Gateway Poll Result":"9","1st Register Value":"18166","2nd Register Value":"50919"},{"Date":"1705599163","Energy Consumed (kWh)":"39329.72","Meter ID":"7","Gateway Poll Result":"9","1st Register Value":"2031","2nd Register Value":"19478"},{"Date":"1705599163","Energy Consumed (kWh)":"131805.58","Meter ID":"8","Gateway Poll Result":"9","1st Register Value":"26161","2nd Register Value":"19707"},{"Date":"1705599163","Energy Consumed (kWh)":"116166.34","Meter ID":"9","Gateway Poll Result":"9","1st Register Value":"37336","2nd Register Value":"19677"},{"Date":"1705599163","Energy Consumed (kWh)":"136570.23","Meter ID":"10","Gateway Poll Result":"9","1st Register Value":"15960","2nd Register Value":"19714"},{"Date":"1705599163","Energy Consumed (kWh)":"41472.99","Meter ID":"11","Gateway Poll Result":"9","1st Register Value":"13560","2nd Register Value":"19486"},{"Date":"1705599163","Energy Consumed (kWh)":"82634.91","Meter ID":"12","Gateway Poll Result":"9","1st Register Value":"40212","2nd Register Value":"19613"},{"Date":"1705599163","Energy Consumed (kWh)":"85259.05","Meter ID":"13","Gateway Poll Result":"9","1st Register Value":"40550","2nd Register Value":"19618"},{"Date":"1705599163","Energy Consumed (kWh)":"147647.23","Meter ID":"14","Gateway Poll Result":"9","1st Register Value":"52912","2nd Register Value":"19724"}]'
https://script.google.com/macros/s/AKfyxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxp2Q/exec
Headers in the Google sheet:
- Date
- Panel
- Energy
- Consumed (kWh)
- Meter ID
- Gateway Poll Result
- 1st Register Value
- 2nd Register Value
- Dummy Col 1
- Dummy Col 2
Solution
Modification points:
From your sample curl command, it seems that the JSON data is an array. But, in your script,
e.postData.contentsis not used as an array.And, in the case of your JSON data in the curl command, the keys of each element are
"Date","Energy Consumed (kWh)","Meter ID","Gateway Poll Result","1st Register Value","2nd Register Value". But, in your script, the keysdate, energyConsumed, slaveID, gatewayPollResult, reg1val, reg2valare used. These keys are not included in your JSON data.Also, your curl command is required to be modified.
From this situation, unfortunately, I'm not sure which is your actual situation. So, in this answer, I would like to believe the JSON data in your curl command is the actual value. By this, I would like to propose a modified script as follows.
When your following JSON data is used,
[{"Date":"1705599163","Energy Consumed (kWh)":"0.00","Meter ID":"1","Gateway Poll Result":"0","1st Register Value":"0","2nd Register Value":"0"},{"Date":"1705599163","Energy Consumed (kWh)":"999259.13","Meter ID":"2","Gateway Poll Result":"9","1st Register Value":"18803","2nd Register Value":"62898"},{"Date":"1705599163","Energy Consumed (kWh)":"4603.56","Meter ID":"3","Gateway Poll Result":"9","1st Register Value":"32081","2nd Register Value":"19084"},{"Date":"1705599163","Energy Consumed (kWh)":"4563.96","Meter ID":"4","Gateway Poll Result":"9","1st Register Value":"18416","2nd Register Value":"19083"},{"Date":"1705599163","Energy Consumed (kWh)":"42749.73","Meter ID":"5","Gateway Poll Result":"9","1st Register Value":"18214","2nd Register Value":"64956"},{"Date":"1705599163","Energy Consumed (kWh)":"31587.45","Meter ID":"6","Gateway Poll Result":"9","1st Register Value":"18166","2nd Register Value":"50919"},{"Date":"1705599163","Energy Consumed (kWh)":"39329.72","Meter ID":"7","Gateway Poll Result":"9","1st Register Value":"2031","2nd Register Value":"19478"},{"Date":"1705599163","Energy Consumed (kWh)":"131805.58","Meter ID":"8","Gateway Poll Result":"9","1st Register Value":"26161","2nd Register Value":"19707"},{"Date":"1705599163","Energy Consumed (kWh)":"116166.34","Meter ID":"9","Gateway Poll Result":"9","1st Register Value":"37336","2nd Register Value":"19677"},{"Date":"1705599163","Energy Consumed (kWh)":"136570.23","Meter ID":"10","Gateway Poll Result":"9","1st Register Value":"15960","2nd Register Value":"19714"},{"Date":"1705599163","Energy Consumed (kWh)":"41472.99","Meter ID":"11","Gateway Poll Result":"9","1st Register Value":"13560","2nd Register Value":"19486"},{"Date":"1705599163","Energy Consumed (kWh)":"82634.91","Meter ID":"12","Gateway Poll Result":"9","1st Register Value":"40212","2nd Register Value":"19613"},{"Date":"1705599163","Energy Consumed (kWh)":"85259.05","Meter ID":"13","Gateway Poll Result":"9","1st Register Value":"40550","2nd Register Value":"19618"},{"Date":"1705599163","Energy Consumed (kWh)":"147647.23","Meter ID":"14","Gateway Poll Result":"9","1st Register Value":"52912","2nd Register Value":"19724"}]
your script is as follows.
Modified script:
function doGet(e) {
return ContentService.createTextOutput("GET request processed successfully").setMimeType(ContentService.MimeType.TEXT);
}
function doPost(e) {
var sheetName = "test log"; // Replace with your sheet name
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var jsonData = e.postData.contents;
var data = JSON.parse(jsonData);
// --- I modified the below script.
var headers = ["Date", "Energy Consumed (kWh)", "Meter ID", "Gateway Poll Result", "1st Register Value", "2nd Register Value"];
var orderedData = [headers, ...data.map(o => headers.map(h => o[h] || null))];
// ---
sheet.insertRowBefore(2);
sheet.getRange(2, 1, orderedData.length, orderedData[0].length).setValues(orderedData);
return ContentService.createTextOutput("Data added successfully").setMimeType(ContentService.MimeType.TEXT);
}
- In this sample, the header is added. But, if you are not required to add the header, please modify
var orderedData = [headers, ...data.map(o => headers.map(h => o[h] || null))];tovar orderedData = data.map(o => headers.map(h => o[h] || null));.
IMPORTANT
When you modify the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the details of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".
Testing:
The sample curl command is as follows. Please replace your Web Apps URL.
curl -L "https://script.google.com/macros/s/AKfyxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxp2Q/exec" \
-d '[{"Date":"1705599163","Energy Consumed (kWh)":"0.00","Meter ID":"1","Gateway Poll Result":"0","1st Register Value":"0","2nd Register Value":"0"},{"Date":"1705599163","Energy Consumed (kWh)":"999259.13","Meter ID":"2","Gateway Poll Result":"9","1st Register Value":"18803","2nd Register Value":"62898"},{"Date":"1705599163","Energy Consumed (kWh)":"4603.56","Meter ID":"3","Gateway Poll Result":"9","1st Register Value":"32081","2nd Register Value":"19084"},{"Date":"1705599163","Energy Consumed (kWh)":"4563.96","Meter ID":"4","Gateway Poll Result":"9","1st Register Value":"18416","2nd Register Value":"19083"},{"Date":"1705599163","Energy Consumed (kWh)":"42749.73","Meter ID":"5","Gateway Poll Result":"9","1st Register Value":"18214","2nd Register Value":"64956"},{"Date":"1705599163","Energy Consumed (kWh)":"31587.45","Meter ID":"6","Gateway Poll Result":"9","1st Register Value":"18166","2nd Register Value":"50919"},{"Date":"1705599163","Energy Consumed (kWh)":"39329.72","Meter ID":"7","Gateway Poll Result":"9","1st Register Value":"2031","2nd Register Value":"19478"},{"Date":"1705599163","Energy Consumed (kWh)":"131805.58","Meter ID":"8","Gateway Poll Result":"9","1st Register Value":"26161","2nd Register Value":"19707"},{"Date":"1705599163","Energy Consumed (kWh)":"116166.34","Meter ID":"9","Gateway Poll Result":"9","1st Register Value":"37336","2nd Register Value":"19677"},{"Date":"1705599163","Energy Consumed (kWh)":"136570.23","Meter ID":"10","Gateway Poll Result":"9","1st Register Value":"15960","2nd Register Value":"19714"},{"Date":"1705599163","Energy Consumed (kWh)":"41472.99","Meter ID":"11","Gateway Poll Result":"9","1st Register Value":"13560","2nd Register Value":"19486"},{"Date":"1705599163","Energy Consumed (kWh)":"82634.91","Meter ID":"12","Gateway Poll Result":"9","1st Register Value":"40212","2nd Register Value":"19613"},{"Date":"1705599163","Energy Consumed (kWh)":"85259.05","Meter ID":"13","Gateway Poll Result":"9","1st Register Value":"40550","2nd Register Value":"19618"},{"Date":"1705599163","Energy Consumed (kWh)":"147647.23","Meter ID":"14","Gateway Poll Result":"9","1st Register Value":"52912","2nd Register Value":"19724"}]'
When this curl command is run with the above script, the following result is obtained on Spreadsheet.
References:
- How to POST a JSON having a single body parameter in FastAPI?
- PHP json_decode multiple arrays or array in array to database
- Spliting a JSON list obj into multiple based on values in python
- Query through JSON Array with multiple values in MySQL
- How to check in json data for any column value is there array/collection present using Angular.js?
- How to JSON serialize ENum classes in Pydantic BaseModel
- How to model a target class for receiving a JSON HTML response in .NET 5
- Embed JSON readably in a URL
- Elixir: How to convert a keyword list to a map?
- Kotlin serialization of a HashMap
- How to get POSTed JSON in Flask?
- How to customise error response in FastAPI?
- jq: how do I handle recursion in this case?
- JsonConvert.SerializeObject changes the sort order of fields in JSON
- Google Tag Manager fires tags in preview/debug, but not on the live site
- Using jq to replace the value of a JSON field is not working correctly
- Update a JSON property in a text column that contains a JSON object
- Deep Copy using Jackson: String or JsonNode
- Sending japanese characters over JSON
- How to escape special characters in building a JSON string?
- Attempting to upgrade rails dependencies causes serializers to raise error: undefined method `model_name' for Hash:Clas
- Why does my code snippet of an JSON file work but the full JSON load does not
- AWS Cloudformation- How to do string Uppercase or lowercase in json/yaml template
- A hosted JSON database that i can Set and Get objects
- python and json files
- SQL Server: Update table based on JSON
- In python, is there a way to extract a embedded json string inside random text?
- TypeScript: serialize BigInt in JSON
- JQ: Filtering for keys
- PHP Composer Autoload Error, PSR-0 not Working