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:
- jq: group and key by property
- How to resolve "TypeError: the JSON object must be str, bytes or bytearray, not NoneType" error, while working on translating with googletrans?
- Create a temporary image from URL not working
- How to Compare nested objects for changes and create a report of changes
- Openweathermap > json / fetch / property is not always available resulting in undefined
- No MediaTypeFormatter is available to read an object of type 'String' from content with media type 'text/plain'
- restassured jsonpath findAll not working as expected
- Import more than 1 json file using mongoimport
- Array from JSON not populating
- Convert Swagger Java Object into JSON/YAML
- ESP32, ArduinoJSON and MQTT: Stuck in process chain
- Jolt Transform add object to array
- How to get jq to output a long dataset?
- Converting from JSON to XML using c#
- JoltTransform: Split each object of input array into 2 separate object and place it in output array
- How to add in response HTTP data in Laravel?
- Vscode Run with debugger error "Launch program *file/path* does not exist
- Json_encode / JMS_Serializer, keep numeric key
- JOLT Transformation, copy an attribute into a dict at same level
- Custom TypeInfoResolver to manage polymorphic deserialization in System.Text.Json
- I am unable to figure out how to parse through an API response consisting of lists and dictionaries to get details - Python
- tsconfig.json: Build:No inputs were found in config file
- Could not determine JSON object type for anonymous type
- Convert or translate a big RDF/XML file to JSON-LD format - HOW?
- UnicodeDecodeError: 'utf8' codec can't decode byte 0xa5 in position 0: invalid start byte
- How to get some values from a JSON string in C#?
- How to Parse Out Nested Dictionaries into Pandas DataFrame
- JSON Naming Convention (snake_case, camelCase or PascalCase)
- How can I decode JSON with a key named 'Type' in Swift?
- Spotfire and Regex- Extracting Several Values Stored in One Column