/**
 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();
}