CODEWITHSUNDEEP
javascriptgoogle-app-maker
alep
alep

Reputation: 135

Export data from Google AppMaker Datasource automatically

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 :)

Upvotes: 3

Views: 1357

Answers (2)

Morfinismo
Morfinismo

Reputation: 5253

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!

Upvotes: 1

Pavel Shkleinik
Pavel Shkleinik

Reputation: 6347

It seems that you are looking for daily database backups. App Maker Team recommends migrating apps to Cloud SQL if you haven't done this so far. Once you start using Cloud SQL as your data backend you can configure backups through Google Cloud Console: https://cloud.google.com/sql/docs/mysql/backup-recovery/backups

Upvotes: 0

Related Questions