Append data to Google Spreadsheet using API and Invoke-RestMethod-CodePudding

I'm doing my first steps with Google API. Currently I have a need to add some data to existing spreadsheet. For some purposes I need to use Invoke-RestMethod instead of any already created modules.

For a test purpose I wanted to add single entry for now. I'm using this code:

$requestUri = "https://sheets.googleapis.com/v4/spreadsheets/$($SpreadsheetID)/values/$("SheetName!A1:F195"):append   

$body = @{
   range = 'SheetName!A1:F195';
   values = @{
       'Field1' = 'Test';
       'Field2' = 'Test';
       'Field3' = 'Test'; 
       'Field4' = 'Test'; 
       'Field5' = 'Test';
       'Link' = 'https://test.com'
   };
};

$test = ConvertTo-Json -InputObject $body

$response = Invoke-RestMethod -Headers @{Authorization = "Bearer $accessToken"} -Uri $requestUri -Method POST -ContentType 'application/json' -Body $test

As a response, I'm receiving this error 400 - Bad request. Sheet already containing some data. Field names in my $body variable are exactly same like in spreadsheet. A196 is first empty cell in my spreadsheet. I was kind of expecting that it will just enter those values into that row basically.

I still didn't know what is wrong, so I decided to use Google API Playground. When I filled everything, request looks like this:

POST /v4/spreadsheets/<SpreadsheetID>/values/SheetName!A1:F195:append HTTP/1.1
Host: sheets.googleapis.com
Content-length: 385
Content-type: application/json
Authorization: Bearer <code>
{
    'range':  'SheetName!A1:F195',
    'values':  {
                   'Field1':'Test',
                   'Field2':'Test',
                   'Field3':'Test',
                   'Field4':'Test',
                   'Field5':'Test',
                   'Link':'https://test.com'
               }
}

As a response I'm getting this:

"code": 400, 
    "details": [
      {
        "fieldViolations": [
          {
            "field": "data.values", 
            "description": "Invalid JSON payload received. Unknown name \"Field1\" at 'data.values': Cannot find field."
          }, 
          {
            "field": "data.values", 
            "description": "Invalid JSON payload received. Unknown name \"Field1\" at 'data.values': Cannot find field."
          }, 
          {
            "field": "data.values", 
            "description": "Invalid JSON payload received. Unknown name \"Field1\" at 'data.values': Cannot find field."
          }, 
          {
            "field": "data.values", 
            "description": "Invalid JSON payload received. Unknown name \"Field1\" at 'data.values': Cannot find field."
          }, 
          {
            "field": "data.values", 
            "description": "Invalid JSON payload received. Unknown name \"Field1\" at 'data.values': Cannot find field."
          }, 
          {
            "field": "data.values", 
            "description": "Invalid JSON payload received. Unknown name \"Link\" at 'data.values': Cannot find field."
          }
        ], 
        "@type": "type.googleapis.com/google.rpc.BadRequest"
      }
    ]

But I really have not clue what is wrong here. I'm fighting with this some time already, but I'm out of ideas ATM. Appreciate if somebody could point me to correct route.

@EDIT I find information that while appending, range should cover existing table. However chaning range to A1:F195 gives exactly same error.

CodePudding user response：

Finally! I found some solution by trying different things. Maybe it's not best one, but definitelly one that actually works. The case was that actually $body in specific form. One that worked (at least not displaying same error) was this:

{
    'range':  'SheetName!A1:F195',
    'values':  [
                  [
                   'Test',
                   'Test',
                   'Test',
                   'Test',
                   'Test',
                   'https://test.com'
                  ]
            ]
}

So putting values in double square brackets and just values. Using this I was able to get another error in API Playground:

{
  "error": {
    "status": "INVALID_ARGUMENT", 
    "message": "'valueInputOption' is required but not specified", 
    "code": 400
  }
}

So after adding ?valueInputOption=USER_ENTERED to $requestUri I was at last able to have row added.

Working code in Powershell looks like this:

$requestUri = "https://sheets.googleapis.com/v4/spreadsheets/$($SpreadsheetID)/values/$("SheetName!A:F"):append?valueInputOption=USER_ENTERED"

$body = @"
{
    'range':  'SheetName!A:F',
    'values':  [
                  [
                   'Test',
                   'Test',
                   'Test',
                   'Test',
                   'Test',
                   'https://test.com'
                  ]
                ]
}
"@

$response = Invoke-RestMethod -Headers @{Authorization = "Bearer $accessToken"} -Uri $requestUri -Method POST -ContentType 'application/json' -Body $body

If somebody know other way how $body could be defined to be acceptable by API - I would gladly see this!

@EDIT Also I have found reason why it wasn't work in first approach. Thanks to more web searching I realized that values in JSON according to documentation is actually array of arrays (which is basically what I "hardcoded" in above example!). "Correct way" of doing it looks like this:

$requestUri = "https://sheets.googleapis.com/v4/spreadsheets/$($SpreadsheetID)/values/$("SheetName!A:F"):append?valueInputOption=USER_ENTERED"

$body = @{
    range = 'SheetName!A:F';
    values = @(
        ,@(
            'Test',
            'Test',
            'Test', 
            'Test', 
            'Test',
            'https://test.com'
        );
    );
};
$test = ConvertTo-Json -InputObject $body

$response = Invoke-RestMethod -Headers @{Authorization = "Bearer $accessToken"} -Uri $requestUri -Method POST -ContentType 'application/json' -Body $test