I've setup a google app script that would be triggered from an external system. This script would fetch the details from the third party system and add them to google sheet row.

function doPost(request) {

  try{
  var jsonString = request.postData.getDataAsString(); //get the request from KF as JSON String
  setLog("\n postData*********************" jsonString "************************************* \n");

 setLog("before the row append");

 ss.appendRow([jsonString["Name"], jsonString["Age"], jsonString["Contact"]]);


  setLog("After the row append");
    
  var returnJson = '{"status": "success"}'; 
  //used to send the return value to the calling function

  setLog("/n returnJson****************************" returnJson "************************************* /n")
  return ContentService.createTextOutput(returnJson).setMimeType(ContentService.MimeType.JSON);
 }

There's absolutely no errors or warnings, but somehow it keeps adding the blank rows into the sheet.

Note: setLog() is a function where I print the values into google doc for debugging.

CodePudding user response：

Maybe the reason your script is not working has to do with the value of jsonString.

I could not find any reference to request.postData.getDataAsString() inside GAS Documentation, so maybe you are trying to call a method on an object which does not support it, which would not raise an Error, but would return undefined.

One quick way to debug this would be to LOG the value (using your custom function or Logger.log(jsonString)) BEFORE you call .appendRow(). Then, you can verify if your variable has the value you expect it to have.

On the other hand, my suggestion is to use this method:

var jsonString = JSON.parse(request.postData.contents) //Get's the content of your request, then parses it

This method is present in the Documentation, and has been consistently working on all of my projects.

CodePudding user response：

I think you should sort the coulmns with google app script. Write this code after ss.appendRow. The column will be sorted and all blank rows gets down.

// Sorts the sheet by the first column, ascending
ss.sort(1)

or if errors try this one also

var fl = SpreadsheetApp.getActiveSpreadsheet();
var sheet = fl.getSheets()[0];
fl.sort(1)