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 | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
piavgh commentedJul 17, 2019
Hi @mrlynn
Where is the variable
columns
here?