Update cells in smartsheet

问题:

I am trying to update some cells in an existing SmartSheet sheet with PHP. It's no problem to add new lines but I can't get the correct JSON for updating.

My code at this moment:

$ch = curl_init("https://api.smartsheet.com/1.1/sheet/1234567890/rows/");

$header = array("Authorization: Bearer xxxxxxxxxxx", 
            "Content-Type: application/json",
            "Assume-User: xxxx%40xxxx.com");
$name = 'MyName';
$fields =  '{"rowNumber":1, "columnId": 1234567890, "value": "'.$name.'", "displayValue": "'.$name.'"}';

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
curl_setopt($ch, CURLOPT_POSTFIELDS,  $fields);
curl_setopt($ch, CURLOPT_POST, 1);
$result = curl_exec($ch);

This results in:

 "{"errorCode":1008,"message":"Unable to parse request. The following error occurred: Unknown attribute \"columnId\" found at line 1, column 45"}"

I tried many options and can't figure this out with the API documentation and can't find any other PHP examples who do the same. Does somebody know how I can update just one specific cell in a row?

回答1:

The Docs

The API documentation for updating a row can be found here. It gives the following example using curl from the command line:

curl https://api.smartsheet.com/1.1/row/{rowId}/cells \
-H "Authorization: Bearer ACCESS_TOKEN" \
-H "Content-Type: application/json" \
-X PUT \
-d '[ {"columnId": 3738748463671172, "value": "Revision 2"}, {"columnId": 5427598323935108, "value": "On Time", "strict": false} ]'

What to Change in the PHP Example

We will need to change your PHP example to send the data following the above stated documentation. So, the following items from the php example will need to be changed:

1. The url needs to be in the format of https://api.smartsheet.com/1.1/row/{rowId}/cells where {rowId} is replaced with the actual row id.
2. We need to send an array of cells. For example, the following is an array of two cells: [{"columnId": 13214124123213, "value": "my new text1"}, {"columnId": 1231231241238, "value": "my new text2"}]
3. We need to send the data as a PUT request.

The Solution

With that in mind we can change the code to the following:

<?php
$ch = curl_init("https://api.smartsheet.com/1.1/row/4407426335172484/cells");

$header = array("Authorization: Bearer 1238123lkjafasdilfasflkj", 
            "Content-Type: application/json",
            "Assume-User: some%40email.com");
$name = 'MyName';
$fields =  '[{"columnId": 4431344890603396, "value": "'.$name.'", "displayValue": "'.$name.'"}]';

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
curl_setopt($ch, CURLOPT_POSTFIELDS,  $fields);
curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
$result = curl_exec($ch);
print_r($result);
?>

Extra Information

You might wonder where the rowId and columnId came from. These two IDs need to be retrieved from the API by getting the sheet. This can be done using Curl, Advanced Rest Client for Chrome or via PHP. The PHP example is below:

<?php
$ch = curl_init("https://api.smartsheet.com/1.1/sheet/1837937135511428");

$header = array("Authorization: Bearer 123l1k2j321lkjasdfa", 
            "Content-Type: application/json",
            "Assume-User: some%40email.com");

curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
$result = curl_exec($ch);
print_r($result);
?>

This request will output something like the following and lists the columns id and rows id.

{
   "id":1837937135511428,
   "name":"test",
   "version":3,
   "columns":[
      {
         "id":4431344890603396,
         "index":0,
         "title":"Primary Column",
         "type":"TEXT_NUMBER",
         "primary":true,
         "width":150
      },
      {
         "id":8934944517973892,
         "index":1,
         "title":"Column2",
         "type":"TEXT_NUMBER",
         "width":150
      },
      {
         "id":138851495765892,
         "index":2,
         "title":"Column3",
         "type":"TEXT_NUMBER",
         "width":150
      },
      {
         "id":4642451123136388,
         "index":3,
         "title":"Column4",
         "type":"TEXT_NUMBER",
         "width":150
      },
      {
         "id":2390651309451140,
         "index":4,
         "title":"Column5",
         "type":"TEXT_NUMBER",
         "width":150
      },
      {
         "id":6894250936821636,
         "index":5,
         "title":"Column6",
         "type":"TEXT_NUMBER",
         "width":150
      }
   ],
   "rows":[
      {
         "id":4407426335172484,
         "rowNumber":1,
         "cells":[
            {
               "columnId":4431344890603396,
               "type":"TEXT_NUMBER",
               "value":"My Name",
               "displayValue":"My Name"
            },
            {
               "columnId":8934944517973892,
               "type":"TEXT_NUMBER",
               "value":"test",
               "displayValue":"test"
            }
         ],
         "expanded":true,
         "createdAt":"2014-06-23T09:32:02-07:00",
         "modifiedAt":"2014-06-23T09:48:55-07:00"
      }
   ],
   "accessLevel":"OWNER",
   "ganttEnabled":false,
   "dependenciesEnabled":false,
   "permalink":"https://app.smartsheet.com/b/home?lx=v75AYzRUICSXF_2oV6V_LA",
   "workspace":{
      "id":6724185599829892,
      "name":"Test"
   },
   "createdAt":"2014-06-23T09:28:58-07:00",
   "modifiedAt":"2014-06-23T09:48:55-07:00"
}