0

I'm developing a code that makes a POST to an API with Google Apps Script.

That API needs in the payload a Array of Arrays.

I use as Array of Arrays directly a GetValues of a range, cause, as I understand the result of this is an Array of Arrays

The API is working if I've tested with curl but when I use the Google Apps Script the API returns me that the payload is not correct.

Here is the code:

function GetUrl()
{
    var values = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getDisplayValues();             
    var api = "https://......";
    
    var headers = {
           "xxxxx-auth": "--------------------------",
           "content-type" : "application/json",
           "cache-control": "no-cache" 
         };
    
    var payload =
    {   
        "token": "XXXXXXXXXXXXXXXXXXXXXXXXX",
        "data" : JSON.stringify(values),
    }
    
    Logger.log(payload);
    
    var options = 
    {
      "headers": headers,
      "method" : "POST",
      "payload" : JSON.stringify(payload)
    };
    
    var response = UrlFetchApp.fetch(api, options);
    var json = JSON.parse(response.getContentText());
    
    Logger.log(json);
}

If I execute this code I will get this logs:

[20-08-14 03:19:37:134 CEST] {token=XXXXXXXXXXXXX, data=[["header1","header2","header3"],["blu","blu2","blu4"],["bla","bla2","bla3"],["blu","blu3","blu5"],["bla","bla3","bla4"]]}

[20-08-14 03:19:37:647 CEST] {Success=false, Data={Code=-1.0, Message=Error: Supplied parameter 'data' must be an array of arrays (cells).}}

I checked with JSON.stringify and without it. ( no differences in the result)

Do you know what I'm doing wrong in the creation of the payload? As I see all should be ok...

Pako LordPakus
  • 101
  • 4
  • 2
    Stringify the payload, not the `data` field, should fix the issue (`fetch` method does not stringify the payload for you) – Oleg Valter is with Ukraine Aug 14 '20 at 02:31
  • 2
    About `The API is working if I've tested with curl`, can you provide the sample curl command? – Tanaike Aug 14 '20 at 08:16
  • 1
    @Oleg Valter About stringify the payload, you are totally correct, but with this "payload" : JSON.stringify(payload) the problem is the same. Thanks anyway! – Pako LordPakus Aug 14 '20 at 08:45
  • @Tanaike The curl line that is working is this: curl -X POST \ https://blablablablalblablablbl -H 'cache-control: no-cache' \ -H 'content-type: application/json' \ -H 'XXXXX: 'XXXXXXXXXXX' \ -d '{ "token": "---------------------------", "data": [ ["header1", "header2", "header3"], ["a", "b", "c"] ] }' I think that is ok, but maybe you find something Thanks for that! – Pako LordPakus Aug 14 '20 at 08:48
  • 2
    @PakoLordPakus - please carefully reread my comment to determine how many times you should stringify :) – Oleg Valter is with Ukraine Aug 14 '20 at 09:23
  • 1
    @Oleg... you are totally right. putting off the stringify of the data and putting the stringify in the payload all is working fine. I was becoming crazy, really thanks! Question, how I mark the response as correct in this case? – Pako LordPakus Aug 14 '20 at 09:33
  • @PakoLordPakus - well, I can publish an answer, but this is a common problem (the only mention of that you need to do that is in the example in docs), so isn't very useful to the knowledge base, just glad you solved it - folks usually expect the payload to be stringified by the method under the hood :) So probably I will find a duplicate target to link to. Btw, as to your question - if someone answers your question, you will have an option to mark as accepted – Oleg Valter is with Ukraine Aug 14 '20 at 12:39

0 Answers0