3

I am posting a JSON to Google Apps Script and want to display a specific value from that JSON in Google Sheets.

I use the following code to get the data into my sheet from this post

function doPost(e) {
    Logger.log("I was called")
    if (typeof e !== 'undefined')
        Logger.log(e.parameter);
    var ss = SpreadsheetApp.openById("ID here")
    var sheet = ss.getSheetByName("Sheet1")
    sheet.getRange(1, 1).setValue(JSON.stringify(e))
    return ContentService.createTextOutput(JSON.stringify(e))
}

My sheet now displays:

{
   "parameter":{

   },
   "contextPath":"",
   "contentLength":20,
   "queryString":"",
   "parameters":{

   },
   "postData":{
      "type":"application/json",
      "length":20,
      "contents":"{\"myValue\":13}",
      "name":"postData"
   }
}

I only want it to display the value for myValue - which is the number 13 in this example.

I've tried a number of things suggested on these forums, I figured I was getting there with the solution from this post, and changed the code to:

function doPost(e) {
    Logger.log("I was called")
    if (typeof e !== 'undefined')
        Logger.log(e.parameter);
    var jsonString = e.postData.getDataAsString(); //added line
    var jsonData = JSON.parse(jsonString); // added line
    var ss = SpreadsheetApp.openById("ID here")
    var sheet = ss.getSheetByName("Sheet1")
    sheet.getRange(1, 1).setValue(JSON.stringify(jsonData.myValue)) //changed "e" to jsonData.myValue
    return ContentService.createTextOutput(JSON.stringify(jsonData.myValue)) //changed "e" to jsonData.myValue
}

This didn't work.

As a simple marketer, I wrestled with this for 2 nights now and feel I am pretty much stuck. So any help would be kindly appreciated. Cheers!

MRI80
  • 35
  • 1
  • 6

1 Answers1

6

You can do something like the following:

function doPost(request) {
  var ss = SpreadsheetApp.openById("ID here")
  var sheet = ss.getSheetByName("Sheet1")
  // Now Parse the body of your post request in to a data object

  var data = JSON.parse (request.postData.contents)
  //Now put the data in the sheet
  sheet.getRange(1, 1).setValue(data['myValue'])
  // Note no need to stringify 
  // now do your other stuff

The body of the post request is available to you in request .postData.contents. Here's the Google documentation

You parse the content string into a JSON object and then access the fields you need.

abir
  • 76
  • 3
  • Thanks for your help, appreciate it! When I try your code, I keep getting an error on line 6 - TypeError: Cannot read property "postData" from undefined - when trying to parse the postdata contents. I changed "request" to "e" on line 6 since I guess this is a mixup from the two example posts I linked to. But both give the error. Any idea what I'm missing here? Thanks! – MRI80 Feb 21 '18 at 15:00
  • My code had a bug. I was cutting and pasting from another code. I have changed it - see if it works now. If it does not, if you post the last version of your code that causing the error, I should be able to help you get passed it. Good luck – abir Feb 22 '18 at 16:12
  • The changed code works like a charm and solved my problem. Thank you for taking the time to figure this one out for me! – MRI80 Feb 23 '18 at 21:48