Skip to content

Instantly share code, notes, and snippets.

@flutter-painter

flutter-painter/spreadsheet_2_Stitch.txt

Last active January 29, 2020 14:02
Show Gist options
  • Save flutter-painter/48fcea8ae4343c009e23d5b4ae300558 to your computer and use it in GitHub Desktop.
Save flutter-painter/48fcea8ae4343c009e23d5b4ae300558 to your computer and use it in GitHub Desktop.
Download ZIP
spreadsheet to Stitch mongoDB
Raw
spreadsheet_2_Stitch.txt
// Create an object which contains keys for each column in the spreadsheet
var columns = { // 0 indexed
id :0,
property1 : 1,
property2 : 2,
}
/****
* This function runs automatically and adds a menu item to Google Sheets
****/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.setActiveSheet(sheet.getSheetByName("the_tab_name"));
var entries = [{
name : "Export your data to Stitch MongoDB",
functionName : "exportEventsToMongoDB"
}
];
sheet.addMenu("Export to Stitch", entries);
};
/****
* Export the events from the sheet to a MongoDB Database via Stitch
****/
function exportEventsToMongoDB() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("the_tab_name");
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange(); // determine the range of populated data
var numRows = range.getNumRows(); // get the number of rows in the range
var data = range.getValues(); // get the actual data in an array data[row][column]
var payload = [];
for (var i=headerRows; i<numRows; i++) {
//var eventIdCell = range.getCell(i+1, columns.event_idi+1);
//var desc = data[i][columns.desc];
// var date = Utilities.formatDate(new Date(data[i][columns.date]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
//var statusUpdateDate = Utilities.formatDate(new Date(data[i][columns.statusUpdateDate]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
// Make a POST request with form data.
var formData = {
id: data[i][columns.id],
property1: data[i][columns.property1],
property2: data[i][columns.property2]
};
payload.push(formData);
}
console.log(payload);
var options = {
'method' : 'post',
'contentType': "application/json; charset=utf-8",
'dataType': "json",
'payload' : JSON.stringify(payload)
};
var webhookUrl = 'https://webhooks.mongodb-stitch.com/api/client/v2.0/app/...'; // TODO complete with your webhook
var insertedIDs = UrlFetchApp.fetch(webhookUrl, options);
console.log(insertedIDs);
// if (desc) {
//var insertID = UrlFetchApp.fetch(webhookUrl, options);
//console.log(insertID);
// eventIdCell.setValue(insertID); // Insert the new event ID
// }
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment