Export data from Google AppMaker Datasource automatically

Question

Does anyone know how we can generate report from data in datasource in Google AppMaker automatically (e.g generate report at 12a.m.) instead of manually click export data in deployments every time user need the report.

I have seen something similar on Exporting data out of Google AppMaker but also no one tried to answer that.

Really appreciate if there is anyone who know how to solve this :)

Answer 1

This can be achieved by using Installable Triggers.

Say for example, you have a model with students data that has three fields; name(string), age(number) and grade(number). On the server script you can write something like this:

//define function to do the data export
function dataExport() {

  //create sheet to populate data
  var fileName = "Students List " + new Date(); //define file name
  var newExport = SpreadsheetApp.create(fileName); // create new spreadsheet
  var header = ["Name", "Age", "Grade"]; //define header
  newExport.appendRow(header); // append header to spreadsheet

  //get all students records
  var ds = app.models.students.newQuery();
  var allStudents = ds.run();

  for(var i=0; i< allStudents.length; i++) {

    //get each student data
    var student = allStudents[i];
    var studentName = student.name;
    var studentAge = student.age;
    var studentGrade = student.grade;

    var newRow = [studentName, studentAge, studentGrade]; //save studen data in a row
    newExport.appendRow(newRow); //append student data row to spreadsheet

  }

  console.log("Finished Exporting Student Data");

}

//invoke function to set up the auto export
function exportData(){

  //check if there is an existing trigger for this process
  var existingTrigger = PropertiesService.getScriptProperties().getProperty("autoExportTrigger");

  //if the trigger already exists, inform user about it
  if(existingTrigger) {

    return "Auto export is already set"; 

  } else { // if the trigger does not exists, continue to set the trigger to auto export data

    //runs the script every day at 1am on the time zone specified
    var newTrigger = ScriptApp.newTrigger('dataExport')
    .timeBased()
    .atHour(1)
    .everyDays(1)
    .inTimezone("America/Chicago")
    .create();

    var triggerId = newTrigger.getUniqueId(); 

    if(triggerId) {
      PropertiesService.getScriptProperties().setProperty("autoExportTrigger", triggerId);
      return "Auto export has been set successfully!";
    } else {
      return "Failed to set auto export. Try again please"; 
    }

  }

}

Then, to delete/stop the auto export, in case you need to, you can write the following on the server script too:

function deleteTrigger() {

  //get the current auto export trigger id
  var triggerId = PropertiesService.getScriptProperties().getProperty("autoExportTrigger");

  //get all triggers
  var allTriggers = ScriptApp.getProjectTriggers();

  //loop over all triggers.
  for (var i = 0; i < allTriggers.length; i++) {

    // If the current trigger is the correct one, delete it.
    if (allTriggers[i].getUniqueId() === triggerId) {

      ScriptApp.deleteTrigger(allTriggers[i]);
      break;

      //else delete all the triggers found  
    } else {

      ScriptApp.deleteTrigger(allTriggers[i]);

    }

  }

  PropertiesService.getScriptProperties().deleteProperty("autoExportTrigger");
  return "Auto export has been cancelled";

}

• You can check the demo app right here.
• The reference to the script properties service is here.
• The reference to the Time Zones list is here.

I hope this helps!