Skip to content

Instantly share code, notes, and snippets.

@mrlynn
Last active October 25, 2023 17:50
Show Gist options
  • Save mrlynn/0e2904376cd9e893ae5cec7207e5bcee to your computer and use it in GitHub Desktop.
Save mrlynn/0e2904376cd9e893ae5cec7207e5bcee to your computer and use it in GitHub Desktop.
Google Sheets Script - Complete Script for Article Stitching Sheets
/****
* Michael Lynn - http://blog.mlynn.org
* Stitching Sheets - Integrating Google Sheets with MongoDB Using MongoDB Stitch
****/
// Create an object which contains keys for each column in the spreadsheet
var columns = { // 0 indexed
type: 2,
date_start: 3,
date_end: 4,
loc: 5,
desc: 0,
url: 1,
status: 6,
owner: 7,
event_id: 8
}
/****
* This function runs automatically and adds a menu item to Google Sheets
****/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.setActiveSheet(sheet.getSheetByName("Events"));
var entries = [{
name : "Export Events to MongoDB",
functionName : "exportEventsToMongoDB"
},{
name: "Remove All Events from MongoDB (Delete Documents)",
functionName: "removeEventsFromMongoDB"
}];
sheet.addMenu("Demo Stitch Sheets Integration", entries);
};
/****
* Export the events from the sheet to a MongoDB Database via Stitch
****/
function exportEventsToMongoDB() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Events");
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]
for (var i=headerRows; i<numRows; i++) {
var eventIdCell = range.getCell(i+1, columns.event_id+1);
var desc = data[i][columns.desc];
var date_start = Utilities.formatDate(new Date(data[i][columns.date_start]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
var date_end = Utilities.formatDate(new Date(data[i][columns.date_end]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
// Make a POST request with form data.
var formData = {
'name': data[i][columns.desc],
'location': data[i][columns.loc],
'date_start': date_start,
'date_end': date_end,
'status': data[i][columns.status],
'owner': data[i][columns.owner],
'url': data[i][columns.url],
'type': data[i][columns.type],
'event_id': data[i][columns.event_id]
};
var options = {
'method' : 'post',
'payload' : formData
};
if (desc) {
var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/import', options);
eventIdCell.setValue(insertID); // Insert the new event ID
}
}
}
/****
* Delete the events from the Calendar and remover the eventID Reference from the sheet - wipeout.
****/
function removeEventsFromMongoDB() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("Events");
var headerRows = 1; // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var numRows = range.getNumRows();
var data = range.getValues();
for (var i=headerRows; i<numRows; i++) {
// Cells are 1 indexed
var eventIdCell = range.getCell(i+1, columns.event_id+1);
// Make a POST request with form data.
var formData = {
'name': data[i][columns.desc],
'location': data[i][columns.loc],
'date_start': data[i][columns.date_start],
'date_end': data[i][columns.date_end],
'status': data[i][columns.status],
'owner': data[i][columns.owner],
'type': data[i][columns.type],
'event_id': data[i][columns.event_id]
};
var options = {
'method' : 'post',
'payload' : formData
};
var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/remove', options);
eventIdCell.setValue(""); // Insert the new event ID
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment