Skip to content

Instantly share code, notes, and snippets.

@NikitaAvvakumov
Last active September 30, 2020 06:23
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save NikitaAvvakumov/10cad2fef7d5ae303b2ba92c0fffec2d to your computer and use it in GitHub Desktop.
Save NikitaAvvakumov/10cad2fef7d5ae303b2ba92c0fffec2d to your computer and use it in GitHub Desktop.
Google Apps Script - send spreadsheet changes to an external API
// provided `onEdit` trigger cannot use `UrlFetchApp.fetch`.
// create a custom-named function, then add it as a new trigger in "Edit" > "Current Project Triggers"
// with events "From spreadsheet" > "On edit"
function customOnEdit(e) {
const range = e.range;
const row = range.getRow();
const sheet = e.source.getActiveSheet();
const data = {
vendor: sheet.getRange(row, 1).getValue(),
sku: sheet.getRange(row, 2).getValue(),
title: sheet.getRange(row, 3).getValue(),
cost: sheet.getRange(row, 4).getValue()
};
Logger.log('data: ' + JSON.stringify(data));
const keys = Object.keys(data);
for (var i = 0; i < keys.length; i++) {
var key = keys[i];
if (data[key].length === 0) {
Logger.log(key + ' contains no data, aborting.');
return;
}
}
Logger.log('All values in place, sending.');
send(data);
}
function send(data) {
// Key-value properties can be added under "File" > "Project properties" > "Script properties"
const scriptProperties = PropertiesService.getScriptProperties();
const baseURL = scriptProperties.getProperty('baseURL');
// const baseURL = 'https://xxxxxxx.ngrok.io'; // for local testing
const authData = {
'email': scriptProperties.getProperty('email'),
'password': scriptProperties.getProperty('password')
};
const authOpts = {
'method': 'post',
'contentType': 'application/json',
'payload': JSON.stringify(authData)
};
const authResp = UrlFetchApp.fetch(baseURL + '/api/login', authOpts);
const authorization = authResp.getHeaders()["Authorization"];
const options = {
'method': 'post',
'contentType': 'application/json',
'payload': JSON.stringify(data),
'headers': {
'Authorization': authorization
}
};
const response = UrlFetchApp.fetch(baseURL + '/admin_api/vendor_products', options);
Logger.log(JSON.stringify(response));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment