CODEWITHSUNDEEP
javascriptjsongoogle-apps-scriptgoogle-sheets
Karina Pysz
Karina Pysz

Reputation: 11

Call API in Google Scripts to populate in Google Sheets

I'm trying to figure out how to send request to URL, so that it populates in Google Sheets. At this moment the log throws error "Order not found". It's my first time writing script for that request. So far I have:

function myFunction() {
  var data = {
            "date":"2021-07-01",
            "reference":"REFERENCE",
            "products":[
              {
               "id":"31565598851174",
               "quantity":15
             },
             {
               "id":"31424655589478",
               "quantity":10
             }
            ]
          }
  var options = {
    'method' : 'post',
   'contentType': 'application/json',
    // Convert the JavaScript object to a JSON string.
    'payload' : JSON.stringify(data)
  };
  var url = "https://stockists.rerootedorganic.co.uk/api/order/?key=example"
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response);
}

I think that the problem might be with the syntax in query. Any help to identify what I'm doing wrong would be amazing, thank you.

Errors:

Execution log
11:45:18 AM Notice  Execution started
11:45:19 AM Info    
{"error":{"message":"Order not found"}}
11:45:19 AM Info    null
11:45:20 AM Info    
{"error":{"message":"Order not found"}}
11:45:20 AM Info    {error={message=Order not found}}
11:45:19 AM Notice  Execution completed

Upvotes: 1

Views: 2424

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15328

Adapt this code

function myFunction() {
  var url = 'https://stockists.rerootedorganic.co.uk/api/order/?key=example&date='
  var when = '2021-07-01' // string format
  var data = JSON.parse(UrlFetchApp.fetch(url+when).getContentText())
  Logger.log(data.order.reference)
  for (var i=0;i<data.order.products.length;i++){
    Logger.log(data.order.products[i].id + ' qty: ' + data.order.products[i].quantity)
  }
}

to populate the sheet

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('** Menu **')
    .addItem('request','myFunction')
    .addToUi();
}
function myFunction() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var d = Utilities.formatDate(sh.getRange('B3').getValue(), "GMT+2", "yyyy-MM-dd")
  var url = sh.getRange('B1').getValue() + '?key=' + sh.getRange('B2').getValue() + '&date=' + d
  var data = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  for (var i=0;i<data.order.products.length;i++){
    sh.appendRow([data.order.reference,data.order.products[i].id,data.order.products[i].quantity])
  }
}

Upvotes: 2

Related Questions