Adding rows with smartsheet API in Google Apps Script

Question

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!

Steve Weil · Answer 1 · 2017-05-12T23:16:39.260

1

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.

edited May 12 '17 at 23:16

answered May 12 '17 at 23:11

Steve Weil

863
5
8

Thanks, Steve, for commenting! I took your advice to use Chrome ARC and it proved the post should work--so thanks for introducing me to ARC. I am in fact trying to POST a new row, and in previous attempts used that method w/out success. Using POST now, the request still fails in Google Apps Script with the same error. Also, the GAS interpreter gives "Bad value" error with the use of JSON.stringify(params). Finally, the reason I used params.body instead of params.payload is payload gives the following error at runtime: {"Attribute provided with invalid value: payload"}. Anybody have ideas? – Bryton Beesley May 15 '17 at 18:15

score 0 · Accepted Answer · answered May 15 '17 at 22:21

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);
}

Thank you, Steve! Tweaking my code to apply JSON.stringify() to the payload element instead of the whole JSON request as you have done here worked. It's puzzling to me why the full request parameter (the "params" variable in my example) failed to be recognized as valid JSON after being stringified, but I expect my puzzlement is the product of inexperience. Anyway, thank you again for providing the solution! — Bryton Beesley, May 18 '17 at 15:52

Adding rows with smartsheet API in Google Apps Script

2 Answers2