Skip to content

Instantly share code, notes, and snippets.

@oeon
Forked from bmcbride/Code.gs
Last active April 13, 2018 00:09
Show Gist options
  • Save oeon/cdce2b47889f6258b0fb19cf1eddabc6 to your computer and use it in GitHub Desktop.
Save oeon/cdce2b47889f6258b0fb19cf1eddabc6 to your computer and use it in GitHub Desktop.
Google Apps Script for exporting CSV files and photos from the Fulcrum Query API to a Drive folder
/**
Title: Google Apps Script for exporting CSV files from the Fulcrum Query API to a Drive folder
Notes: Be sure to manually run the exportData() function at least once to authorize the script. Set a timed trigger to automate exports.
Author: Bryan R. McBride
Contributor: Joe Larson, exportPhotos()
**/
var fulcrumToken = "abcdefghijklmnopqrstuvwxyz";
var fulcrumFormName = "My App";
var filesFolder = "1ByvB7rSq_Pi2AE_JvqRFm6zUETbdJ8dG";
function exportPhotos() {
// update query to include the Data Names of the Photo Fields you want exported. This example has 1 field named 'photos'
var query = "SELECT photos FROM \"" + fulcrumFormName + "\" WHERE _server_updated_at >= NOW() - '1 day'::INTERVAL ORDER BY _server_updated_at DESC";
var url = "https://api.fulcrumapp.com/api/v2/query/";
var options = {
"method": "POST",
"headers": {
"X-ApiToken": fulcrumToken,
"Accept": "application/json"
},
"contentType": "application/json",
"payload": JSON.stringify({
"q": query,
"format": "json"
})
};
var data = UrlFetchApp.fetch(url, options);
var records = JSON.parse(data);
if (records && records.rows.length) {
for (var i = 0; i < records.rows.length; i++) {
var photos = records.rows[i].photos;
for (var j = 0; j < photos.length; j++) {
var photo_id = photos[j];
var fulcrumShareUrl = 'https://api.fulcrumapp.com/api/v2/photos/' + photo_id + '/thumbnail.jpg?token=' + fulcrumToken
var blob = UrlFetchApp.fetch(fulcrumShareUrl);
var content = blob.getAs('image/jpeg');
var jpgFile = DriveApp.createFile(content);
jpgFile.setName(photo_id + ".jpg");
DriveApp.getFolderById(filesFolder).createFile(jpgFile);
}
}
}
}
function exportData() {
var query = "SELECT * FROM \"" + fulcrumFormName + "\" WHERE _server_updated_at >= NOW() - '1 day'::INTERVAL ORDER BY _server_updated_at DESC";
var url = "https://api.fulcrumapp.com/api/v2/query/";
var options = {
"method": "POST",
"headers": {
"X-ApiToken": fulcrumToken,
"Accept": "application/json"
},
"contentType": "application/json",
"payload": JSON.stringify({
"q": query,
"format": "csv"
})
};
var csvFile = UrlFetchApp.fetch(url, options);
var date = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd");
DriveApp.getFolderById(filesFolder).createFile(date + ".csv", csvFile, MimeType.CSV);
exportPhotos();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment