doPost() from Google Apps Script - Passing Sheet Data into JSON

[Jordan_Davis1]

I have CSV’s sent to me on a daily basis which need to be added to a table in my app. I’m having them automatically sent to a folder on my drive which will trigger a doPost script I’m working on. I have the spreadsheet importing and converting to JSON, however, I can’t figure out how to pass all of the rows of my spreadsheet into the POST request.

function doPost() {
var apiurl = ‘https://api.appsheet.com/api/v2/apps/MYAPPID/tables/MYTABLE/Action’;

var result = {};
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘THE SHEET I WANT TO IMPORT’)
.getDataRange()
.getValues();

result.sheet = makeObject(sheet);

Logger.log(result.sheet);

var body = {
“Action”: “Add”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Eastern Standard Time”,
“RunAsUserEmail”: “myemail@ICANTCODEFORSHIT.com”
},
“Rows”: [{
“MY COLUMN NAME 1”: HOW DO I REFERENCE COLUMN 1 OF MY SHEET?,
“MY COLUMN NAME 2”: WTF DO I PUT HERE? WHY CAN I NOT FIGURE THIS OUT?

  }

]
}}
var options = {
‘headers’ : {‘ApplicationAccessKey’ : ‘MYAPPLICATIONACCESSKEY’,
‘Accept’ : ‘application/json, application/geo+json, application/gpx+xml, img/png; charset=utf-8’,
‘Authorization’: ‘’
},

	'method' : 'POST',
	'muteHttpExceptions': true,
	'contentType': 'application/json',
  'payload': JSON.stringify(body),
	
};

console.log(JSON.stringify(options));
var response = UrlFetchApp.fetch(apiurl, options);

}

[Bellave_Jayaram]

You didn’t provide the makeObject() code but that’s where you would return the JSON that would go into the Rows.
See https://stackoverflow.com/questions/47555347/creating-a-json-object-from-google-sheets

[Jordan_Davis1]

Thanks for the tip! How do I format the “Rows” section?
That’s what I can’t figure out. Would it be something like this?

“Column 1”: object[0],
“Column 2”:object[1]

[Bellave_Jayaram]

...
“Rows”: [getJsonFromSheet(sheet)],
...

where

function getJsonFromSheet(data)
{

  var obj = {};
  var result = [];
  var headers = data[0];
  var cols = headers.length;
  var row = [];

  for (var i = 1, l = data.length; i < l; i++)
  {
    // get a row to fill the object
    row = data[i];
    // clear object
    obj = {};
    for (var col = 0; col < cols; col++) 
    {
      // fill object with new values
      obj[headers[col]] = row[col];    
    }
    // add object in a final result
    result.push(obj);  
  }

  return result;  

}

[Jordan_Davis1]

omg, that took so long for me to figure out how to plug everything together but it finally works. Thank you so much!!!

[Bellave_Jayaram]

You’re welcome.