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
{"document":[{"document_number":8542,"name":"VIOLET BEARINGS","context_id":5,"total_cost":7213.03,"total_tax":415.45,"total_tax2":0,"total_price":8309.08,"total_transactions":0,"issue_date":"2022-12-13T00:00:00 00:00","expiry_date":null,"shipping_address":"(Same as above)","date_sent":null,"shipped_percent":null,"status_id":9,"public_note":"","attention":"GLEN","payment_terms":"","id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","customer_id":"5b0d881b-6a88-46e5-998c-06330a8e0006","user_id":"5b0d75b3-e9cc-4899-bf99-77610a8e0006","associated_document_id":"","created":"2022-12-13T21:21:43 00:00","modified":"2022-12-21T16:01:04 00:00","status":{"id":9,"name":"unpaid"},"context":{"id":5,"name":"invoice"},"customer":{"id":"5b0d881b-6a88-46e5-998c-06330a8e0006","name":"TestClient","customer_number":1889,"billing_address_1":"Add1","billing_address_2":"Add2","billing_city":"TestCity","billing_region":"TestLoc","billing_postal_code":"TestPost","billing_country":"CANADA","shipping_address_1":"Add1","shipping_address_2":"Add2","shipping_city":"TestCity","shipping_region":"TestLoc","shipping_postal_code":"TestPost","shipping_country":"CANADA"},"user":{"first_name":"test","last_name":"test"},"document_items":[{"id":"0ea11906-f197-4eb4-971e-715d4dc77ab2","name":"B-0832","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"JGE/H/K DOOR GASKET","quantity":8,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":38.1,"price":43.81,"discounted_price":0,"weight":2,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-15T15:43:57 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"2b7e9376-ed70-4e75-95c9-6237aad8cfc0","name":"B-0770","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"MAIN BEARING JGE/H/K","quantity":8,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":282.73,"price":325.14,"discounted_price":0,"weight":6,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-15T15:43:57 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"391c2ec1-e02d-4390-9a10-429c2ea460fc","name":"B-2082","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"ROD BEARING JGE/H/K","quantity":8,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":282.73,"price":325.14,"discounted_price":0,"weight":5,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-15T15:43:57 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"725fb97d-243f-44aa-98d0-50d903871ae4","name":"B-0776","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"THRUST PLATE JGE/H/K","quantity":2,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":878.44,"price":1010.21,"discounted_price":0,"weight":7,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-15T15:43:57 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"956cf37e-3921-4cad-94bf-670cdedd2d17","name":"B-1032","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"JGK DOOR GASKET","quantity":8,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":17.21,"price":19.79,"discounted_price":0,"weight":3,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-15T15:43:57 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"c2b74c55-465b-4bee-84a1-6c9e8e5bd102","name":"A-0661","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"OIL FILTER","quantity":3,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":24.5,"price":28.18,"discounted_price":0,"weight":4,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-15T15:43:57 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"de2c8695-9589-4856-be7a-d3979a776646","name":"FREIGHT CHARGE","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":1,"service_category_id":"5e6a78e2-cf6c-437c-ac52-0c3a0a8e000a","description":"RE:E2920103","quantity":1,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":70.64,"price":95.35,"discounted_price":0,"weight":8,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-21T15:34:51 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null},{"id":"eab3c271-20ce-4c4e-af92-8553425eaa71","name":"C-6200","document_id":"0b24e8d2-e7cc-4441-abd7-337c8a2c6cf1","item_id":null,"unit_id":4,"service_category_id":"5dc2f6d2-27e0-4dac-a2b7-39080a8e0008","description":"TOP COVER GASKET","quantity":1,"quantity_partially_received":0,"quantity_partially_shipped":0,"tax":5,"tax2":0,"discount_percent":0,"cost":345.85,"price":397.73,"discounted_price":0,"weight":1,"modified":"2022-12-21T16:01:04 00:00","created":"2022-12-13T21:21:43 00:00","item_variations_location_id":null,"item_variation_data":null,"delete":false,"item":null}]}]}
I would like to output the JSON content to look like this on my google sheet
Reference:
You may further study about accessing JSON objects date in this article: JSON Object Literals
CodePudding user response：
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.