smartsheet api update rows error 1004. you are not authorized

Question

I am trying to use powershell and the API to update a cell, but I get an error: 1004 You are not authorized to perform this action. I've elided the IDs . . .

I have a 30 day trial. Do I need to buy in to some level of support before I can use the API to do updates or what? The sheet is on my account and I am the owner. Is Write Sheets role not included in Owner?

I am using powershell 5.1 to make the http request – here is my script. It first gets the correct sheet ID, then puts the rows and columns I am interested in into hashes, and then tries to update the sheet, but although I am the owner, I am unauthorized.

# access account of jjj.mmm@ddd.com
$apiKey = "3ccfgk..."
$url = "https://api.smartsheet.com/2.0/sheets/"
$get_headers = @{"Authorization" = "Bearer " + $apiKey}
$put_headers = @{"Authorization" = "Bearer " + $apiKey, "Content-Type: application/json"  }

# get all the sheets
$response = Invoke-RestMethod -Uri $url -Headers $get_headers
$rd = $response.data
$json = ConvertTo-Json $rd

# find the Forecast sheet
$sheet_id = $rd | Where {$_.name -eq "Forecast Intermediary Sheet"} | Select -ExpandProperty id
"Sheet ID: $sheet_id"

#get the Forecast sheet
$surl = "$url$sheet_id"
$surl
$response = Invoke-RestMethod -Uri $surl -Headers $get_headers
$response | Format-List

# iterate over the rows and get the active ones
$active_rows = @{}
foreach ($row in $response.rows) {
    if ($row.rowNumber -ge 14) {
        if ($row.cells[2].value -Match "Active") {
            $active_rows.Add($row.cells[2].value, $row.id)
        }
    }
}

# get the col_ids by date
$date_cols = @{}
foreach ($c in $response.columns) {
    if ($c.title -as [datetime]) {
        $d = $c.title -replace "/20", "/"
        $ds = [datetime]::parseexact($d, 'mm/dd/yy', $null)
        $date_cols.Add($ds.Tostring("yyyy-mm-dd"), $c.id)
    }

}


"UPDATE:"
$rowid = $active_rows["SSL PS Active"]
$colid = $date_cols["2017-11-01"]
"row: $rowid, col: $colid"

$json = '[{ "id": "'+$rowid+'", "cells": [{"columnId": "'+$colid+'","value": "23"} }]'
"JSON: $json"
$purl = "$surl/rows"
"PUT URL: $purl"
$r = Invoke-RestMethod -Method "PUT" -uri $purl -Headers $put_headers -Body $json
$r

Below is the output of the script. The error seems to indicate it is syntactically correct, but OWNER is insufficient permissions.

Sheet ID: 5724...
https://api.smartsheet.com/2.0/sheets/5724....
UPDATE:
row: 5668..., col: 5118...
JSON: [{ "id": "5668...", "cells": [{"columnId": "5118...","value": "23"} }]
PUT URL: https://api.smartsheet.com/2.0/sheets/5724.../rows


Invoke-RestMethod : {
  "errorCode" : 1004,
  "message" : "You are not authorized to perform this action.",
  "refId" : "14a7o8lu9sfyj"
}
At \\winfiles\jmoore\powershell\ss_api.ps1:58 char:6
+ $r = Invoke-RestMethod -Method "PUT" -uri $purl -Headers $put_headers ...
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], 
    WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodComand

Here is the meta data of my sheet

Sheet ID: 5724...
https://api.smartsheet.com/2.0/sheets/5724....


id                         : 5724...
name                       : Forecast Intermediary Sheet
version                    : 2
totalRowCount              : 37
accessLevel                : OWNER
effectiveAttachmentOptions : {GOOGLE_DRIVE, DROPBOX, ONEDRIVE, EGNYTE...}
ganttEnabled               : False
dependenciesEnabled        : False
resourceManagementEnabled  : False
cellImageUploadEnabled     : True
userSettings               : @{criticalPathEnabled=False; displaySummaryTasks=True}
permalink                  : https://app.smartsheet.com/b/home?lx=LCTEj6F0xWNKWWxFUuLH0w
createdAt                  : 2017-11-22T22:34:51Z
modifiedAt                 : 2017-11-22T22:34:51Z
columns                    : {@{id=6985...; index=0; title=Column1; type=TEXT_NUMBER; primary=True; 
                             validation=False; width=64}, @{id=1356...; index=1; title=Column2; 
                             type=PICKLIST; options=System.Object[]; validation=False; width=64}, 
                             @{id=5859...; index=2; title=Column3; type=TEXT_NUMBER; validation=False; 



....

Kim Brandl · Accepted Answer · 2017-11-23T07:34:51.733

You should be able to make API calls using an API Access Token that's owned by a trial Smartsheet account, and you can most certainly update a sheet that you own using an API Access Token you own.

I'd suspect that the 1004 error response is being caused by the fact that the contents of $put_headers isn't formatted properly. i.e., Smartsheet isn't able to accurately parse the headers you're attempting to specify in $put_headers in order to identify the Authorization header and read its value. When Smartsheet doesn't see the Authorization header in an inbound API request, it'll respond with the 1004 You are not authorized to perform this action. error response.

To troubleshoot this issue, I'd suggest that you use a tool like Fiddler to examine the outbound "Update Rows" request, paying special attention to what headers are present in the request. Then, if you discover that the Authorization header is absent from the request, you'll need to figure out how to specify multiple request headers in PowerShell, and update your code accordingly re how you're setting the value of $put_headers.

Update (adding PowerShell code):

I'm no PowerShell expert, but you might try replacing this line:

$put_headers = @{"Authorization" = "Bearer " + $apiKey, "Content-Type: application/json"}

With these lines instead:

$put_headers = @{}
$put_headers.Add("Authorization", "Bearer " + $apiKey)
$put_headers.Add("Content-Type", "application/json")

score 0 · Answer 2 · answered Nov 25 '17 at 05:44

Thanks @kim-brandl

Or if you want to build the headers in a single command, you need to use syntax like this: $put_headers = @{"Authorization" = "Bearer " + $apiKey; "Content-Type" = "application/json" }

Note also that your json payload has a few problems including:

Unmatched brackets
Extra square brackets on the outside (the body is an object, not an array)
Column and row ids must be numbers, not strings

I suggest you test the payload in a tool like postman first.

smartsheet api update rows error 1004. you are not authorized

2 Answers2