Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google Sheets Script to Export Data to MongoDB Stitch API Service
/****
* 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
}
}
}
@piavgh

This comment has been minimized.

Copy link

piavgh commented Jul 17, 2019

Hi @mrlynn

var eventIdCell = range.getCell(i+1, columns.event_id+1);

Where is the variable columns here?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.