Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Get specific data from Salesbinder API JSON to a Google sheet

YvanL
December 23, 2022
212 views
0 votes
2 Answers

Hi what I’m trying to do is get the data from a Salesbinder invoice API (Invoice # is taken from Sheet2 Cell A1) output the data to a Google sheet (sheet Cell A2)
here’s the code i’m using to get data from Salesbinder API

function fetching() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var InvNumber = sheet2.getRange('A1').getValue();
  var USERNAME = 'APIKey';
  var PASSWORD = 'x';
  
  var url = 'https://mydomain.salesbinder.com/api/2.0/documents.json?documentNumber='+InvNumber+'&contextId=5';
  var headers = {
    "Authorization": "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };

 
  let response = UrlFetchApp.fetch(url, { headers });
  Logger.log(response.getContentText());

}

The result I get from the data is like this

I would like to output the JSON content to look like this on my google sheet

https://docs.google.com/spreadsheets/d/18x1cyztf5SgnKZHjqgWx2iP7SzQAksOzE_EVZoUOcy8/edit#gid=0

Any help would be greatly appreciated
Thanks

Answers

In your script, when the value of response.getContentText() is your showing data, how about the following modification?

From:

Logger.log(response.getContentText());

To:

Logger.log(response.getContentText());

// I added the below script.
const value = JSON.parse(response.getContentText());
const addValues = [value.document[0].customer.name, value.document[0].issue_date, value.document[0].attention, value.document[0].name];
const res = value.document[0].document_items.map(o => [value.document[0].document_number, ...["name", "description", "quantity", "price", "discounted_price"].map(h => o[h]), ...addValues]);
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
sheet.getRange(sheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);

Please set your sheet name to getSheetByName("Sheet1").
Unfortunately, I cannot find External PO# you expect from your data.

Suggestion: Clean and Extract the Required Data

The first thing I noticed is that your current script has already extracted a single JSON object from Salesbinder. However, the format returned is hard to read. You may use JSON beautify sites online to rearrange and analyze the JSON object you have produced like JSON Viewer (DISCLAIMER: I am not affiliated with the website, I just find it helpful to share it to others due to its helpful features).

Script

Afterwards, you may be able to access the data you wanted by accessing the JSON object. For your table, you may use the following:

  var salesBinderInvoiceNumVal = object.document[0].document_number;
  var itemNameVal = object.document[0].document_items[0].name;
  var descriptionVal = object.document[0].document_items[0].description;
  var quantityVal = object.document[0].document_items[0].quantity;
  var priceVal = object.document[0].document_items[0].price;
  var discountPercentVal = object.document[0].document_items[0].discount_percent;
  var clientNameVal = object.document[0].customer.name;
  var issueDateVal = object.document[0].issue_date;
  var attentionVal = object.document[0].attention;
  var nameVal = object.document[0].name;

Note: The External PO number can’t be found since there are no given values in your sample table.

But before doing so, you may want to store the generated JSON object to a variable. Thus, you may change:

Logger.log(response.getContentText());

to:

var object = response.getContentText();

Afterwards, you may add the setValues() function to add the values to the last row of your table. Thus, your script should be somewhat like this:

function fetching() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var InvNumber = sheet2.getRange('A1').getValue();
  var USERNAME = 'APIKey';
  var PASSWORD = 'x';

  var url = 'https://mydomain.salesbinder.com/api/2.0/documents.json?documentNumber=' + InvNumber + '&contextId=5';
  var headers = {
    "Authorization": "Basic " + Utilities.base64Encode(USERNAME + ':' + PASSWORD)
  };

  let response = UrlFetchApp.fetch(url, { headers });

  //Changes start here
  var object = response.getContentText(); //Store the JSON object to this variable
  var lastRow = sheet.getLastRow(); //get the last row

  //required values
  var salesBinderInvoiceNumVal = object.document[0].document_number;
  var itemNameVal = object.document[0].document_items[0].name;
  var descriptionVal = object.document[0].document_items[0].description;
  var quantityVal = object.document[0].document_items[0].quantity;
  var priceVal = object.document[0].document_items[0].price;
  var discountPercentVal = object.document[0].document_items[0].discount_percent;
  var clientNameVal = object.document[0].customer.name;
  var issueDateVal = object.document[0].issue_date;
  var attentionVal = object.document[0].attention;
  var nameVal = object.document[0].name;

  //add the required values to a 2d array
  var output = [[salesBinderInvoiceNumVal, itemNameVal, descriptionVal, quantityVal, priceVal, discountPercentVal, clientNameVal, issueDateVal, attentionVal, nameVal]];
  sheet.getRange(lastRow+1, 1, 1, output[0].length).setValues(output); //add the 2d array to the last row of your table

}

Output:

Since your script only gets the value of one cell, I assumed that it only fetches one item or one row to your table. NOTE: I only processed the given JSON object in your post since I have no access/account in Salesbinder. I did assume that it was the output of your current script.

Reference:

You may further study about accessing JSON objects date in this article: JSON Object Literals

Please signup or login to give your own answer.

Click here to cancel reply.