Skip to content

Instantly share code, notes, and snippets.

@mrlynn
Created April 5, 2020 15:22
Show Gist options
  • Save mrlynn/246982412a0b6315151297ffe5bff2de to your computer and use it in GitHub Desktop.
Save mrlynn/246982412a0b6315151297ffe5bff2de to your computer and use it in GitHub Desktop.
Query MongoDB Stitch API for Data from A Google Apps Script - maintaining timeout to avoid overloading API
/**
|* Insert this code into your Google Sheets Script Editor
|* From there, you can insert into a cell in the sheet =fetchCode("somevalue")
|* This will return the value fetched from the database and set a last date fetched.
|* Next time you want to fetch - it will compare the last date fetched. If it's more than a day
|* It will fetch again - otherwise, not.
**/
function fetchCode(code) {
if (!code || code == "") {
return;
}
var codeProperties = PropertiesService.getUserProperties();
if (codeProperties.getProperty(code)) {
var days = diffInDays(codeProperties.getProperty(code), new Date())
Logger.log("Days since update: " + days);
if (days < -1) {
return; // too soon.
}
} else {
codeProperties.setProperty(code, new Date())
Logger.log("Set date to " + new Date());
}
// change this url to the url you created from your 3rd party http webhook
var url = "https://webhooks.mongodb-stitch.com/api/client/v2.0/app/<stitch-app-id>/service/api/incoming_webhook/api?param=" + code + "&secret=<YOURSECRE>";
var response = UrlFetchApp.fetch(url, { 'muteHttpExceptions': false });
if (response != false) {
var data = JSON.parse(response.getContentText());
if (data.length > 0) {
var remaining = data[0].credits[0].remaining_amount_dollars.$numberDouble;
Logger.log("Remaining: " + remaining);
return remaining;
//Get the date
var json = response.getContentText();
if (!Array.isArray(json)) {
json = [json];
}
} else {
return;
}
}
}
/*
|* Compare two dates
*/
function diffInDays(d1, d2) {
var dt1 = new Date(d2), // today's date
dt2 = new Date(d1); // your date from API
// get milliseconds
var t1 = dt1.getTime(),
t2 = dt2.getTime();
var diffInDays = Math.floor((t1 - t2) / (24 * 3600 * 1000));
// 24*3600*1000 is milliseconds in a day
console.log(diffInDays);
return diffInDays;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment