CODEWITHSUNDEEP
jsongoogle-apps-scriptsmartsheet-api
Bryton Beesley
Bryton Beesley

Reputation: 179

Adding rows with smartsheet API in Google Apps Script

I'm having the same difficulty as user:itsallgood in this link--I get the following error when I submit the below request to add a row.

{"errorCode":1008,"message":"Unable to parse request. The following error occurred: Request body must be either a JSON object or JSON array."}

I believe the data is formatted correctly, and I have validated that it is valid JSON at jsonformatter.curiousconcept.com.

var params = {  
   "headers":{  
      "authorization":"Bearer <<removed for public posting>>"
   },
   "contentType":"application/json",
   "method":"PUT",
   "body":[  
      {  
         "toBottom":true,
         "cells":[  
            {  
               "columnId":4209581015492484,
               "value":"New row"
            }
         ]
      }
   ]
}
var response = UrlFetchApp.fetch("https://api.smartsheet.com/2.0/sheets/<<sheet key removed>>/rows", params)

The answer provided to itsallgood is that the problem is due to a bug with the API 1.1 endpoint and that 2.0 will solve it. However, I am using the API 2.0 endpoint and still getting the error.

If someone can help solve this riddle, I'll be much obliged!

Upvotes: 0

Views: 1317

Answers (2)

Steve Weil
Steve Weil

Reputation: 873

Here's a complete example:

function addRow(){
var url ="https://api.smartsheet.com/2.0/sheets/5670346721388420/rows";

var data ={
        "toBottom": true,
        "cells": [
            {
                "value": "New data",
                "columnId": 5759377954105220
            }
        ]
    };

var options ={
        "headers": {
            "authorization": "Bearer ll352u9jujauoqz4fstvsae05"
        },
        "method": "post",
        "contentType": "application/json",
        "payload": JSON.stringify(data)
    };
Logger.log("About to call: " + url);

var response = UrlFetchApp.fetch(url, options);

Logger.log("Response: " + response);
}

Upvotes: 0

Steve Weil
Steve Weil

Reputation: 873

It's much easier to test with an interactive tool such as Postman, Chrome Advanced Rest Client, or cUrl. I'm not sure what your script is actually sending over the wire.

Several observations

The UrlFetch docs example includes an explicit conversion from JS object to JSON string:

var data = { ... }

var params = {
    'method' : 'post',
    'contentType': 'application/json',
    // Convert the JavaScript object to a JSON string.
    'payload' : JSON.stringify(data)
};

Note also that the sample sets params.payload while you are setting params.body

Also, PUT /sheets/{sheetId}/rows is used to update one or more existing rows. So a row Id is required on each row object in the payload. See example in the docs.
Alternatively, you could POST a new row.

Upvotes: 1

Related Questions