Sending JSON array to webservice from Google Sheet Script

Question

We have a webservice that returns product information. The service expects a JSON array in the post data... The

A sample script from a simple HTML test web page is as follows (this works as expected):

    &ltscript src="//code.jquery.com/jquery-1.11.2.min.js"></script>

    <script type="text/javascript">

    $(document).ready(function(){

    // request is array of product IDs e.g. pid:[71,72,74]

    // request is array of search terms e.g. find:['apples','oranges','pears']
    // request can contain requested fields from products
    // fields:['CostPrice','Published','title','Price']

    jQuery.ajax({
        url : 'http://somewebsite.com/__getProductData',
        type: 'POST',
        dataType : 'json',
        data: { pid:[658,71,6,700], fields:['pid','CostPrice','type','published','title'] },

    success:function(data) { 

        // do something with the response...    

        });


     },

    error: function(  jqXHR,  textStatus, errorThrown) {alert('Error Status:' + textStatus + '    Error:'+errorThrown); }

    }

    );


    });

    </script>

The web service, written in PHP, receives this correctly. Dumping (print_r) the data received by the web service from the client results in the following array:

    Array ( 
    [pid] => Array ( [0] => 658 [1] => 71 [2] => 6 [3] => 700 ) 

    [fields] => Array ( [0] => pid [1] => CostPrice [2] => type [3] => type [4] => published [5] => title ) 

Now to the problem.... I'm trying to call the webservice from a Google Sheet Script as follows:

    function getProduct( pid,datetime) {

      // 
      var url = 'https://somewebsite.com/__getProductData';

      //pid:[658,71,6,700], fields:['pid','CostPrice','type','published','title']

      var nids = new Array( 658,71,6,700 );

      var fields = ['pid','CostPrice','type','published','title']
      var payload =
          { 
          pid : nids,
          fields: fields

       };

       var options =
       {
         "method": 'POST',

         "payload": payload


       };

      var response = UrlFetchApp.fetch(url, options);

     Logger.log(response);


    }

When the PHP web service is called from Google Sheets script the JSON array is not receive correctly:

    Array ( [data] => { pid=[Ljava.lang.Object;@1bfbb500} fields=[Ljava.lang.Object;@3ffbb500})

I expect this is something associated with encoding or headers... I've tried most combinations of headers and contentType but to no avail.... e.g.

    var headers = { "Accept":"json", 
              "Content-Type":"application/json", 
             };

Suggestions anyone?

Answer 1

var options = {
  "method":  'POST', 
  "payload": JSON.stringify(payload)
};

Or stringify each of the values for pid & fields.

Answer 2

Jonathan - your suggested comment is the solution....

This didn't work:

var options =
 {
 "method": 'POST',
 "payload": JSON.stringify(payload)
 };

but stringify on the payload array elements worked (note that simple elements worked without stringify e.g. 'text' : "some text here") :

 var nids = [658,71,6,700 ];
 var fields = ['pid','CostPrice','type','published','title']
 var payload =
      { 
      pid : JSON.stringify(nids),
      fields: JSON.stringify(fields)
   };
var options =
 {
 "method": 'POST',
 "payload": payload
 };

Answer 3

I thinkg I have solved the same issue: How to send a post request with an array in payload by urlfetchapp

the payload has to be prepared in this way! I have found this solution during I was making a workaround PHP script.

  var payload =
      {       
        "fields[0]":"firstString",
        "fields[1]":"anotherString in array",        
      }

Not very intuitive, but it worked for me great!