Skip to content

Instantly share code, notes, and snippets.

@bmcbride bmcbride/Code.gs
Last active Aug 16, 2018

Embed
What would you like to do?
Google Apps Script for importing CSV files from Drive into Fulcrum
/**
Title: Google Apps Script for importing CSV files from Drive into Fulcrum
Notes: Be sure to manually run the getFiles() function at least once to authorize the script. Set a timed trigger to automate imports.
Author: Bryan R. McBride
--CSV file format--
latitude,longitude,name,color
27.770787,-82.638039,Building 1,red
**/
var fulcrumToken = "abcdefghijklmnopqrstuvwxyz";
var fulcrumFormID = "a123b456-7c89-0123-4567-de8f910g0123";
var filesToImportFolder = "1_NGfsxszanv2evVJgfKMXxmU54SZ92FW";
var importedFilesFolder = "1l0KX8VWjhijsEylnnhGCVIdRmSzatglm";
function getFiles() {
// get the CSV files in the specified Drive folder
var files = DriveApp.getFolderById(filesToImportFolder).getFiles();
// loop through the files
while (files.hasNext()) {
// get the file
var file = files.next();
// parse the CSV
var data = Utilities.parseCsv(file.getBlob().getDataAsString());
// pass the file and the parsed data to the import function
importData(file, data);
}
}
function importData(file, data) {
// loop through the CSV rows skipping the first row headers
for (var i = 1; i < data.length; i++) {
// build the record JSON expected by Fulcrum
var record = {
"record": {
"form_id": fulcrumFormID,
"status": "Imported", // hard coded status for imported records
"latitude": data[i][0],
"longitude": data[i][1],
"form_values": {
"2832": data[i][2], // name
"0dda": {
"choice_values": [
data[i][3] // color
]
}
}
}
};
// POST the record JSON to the Fulcrum API
UrlFetchApp.fetch("https://api.fulcrumapp.com/api/v2/records.json", {
"method": "POST",
"contentType": "application/json",
"payload": JSON.stringify(record),
"headers": {
"X-ApiToken": fulcrumToken,
"Accept": "application/json"
}
});
}
// remove the CSV file from the "Files to Import" folder
file.getParents().next().removeFile(file);
// add the removed CSV file to the "Imported Files" folder
DriveApp.getFolderById(importedFilesFolder).addFile(file);
// send the email notification with the record count
sendEmail(data.length - 1);
}
function sendEmail(count) {
MailApp.sendEmail({
to: "someone@yourdomain.com",
name: "Fulcrum Import Notifier",
subject: count + " records imported into Fulcrum",
htmlBody: "Congratulations! " + count + " records have been successfully imported into Fulcrum."
});
}
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.