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

I think that's not possible... You could save the data sources in different spreadsheets, but i don't know if that solves your problem... — jbra95, Mar 01 '18 at 11:44
I've posted answer to the question you mentioned in your question feel free to take a look. — Pavel Shkleinik, Mar 01 '18 at 16:34

Morfinismo · Accepted Answer · 2018-03-02T12:37:20.413

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!

Please, note that this solution doesn't export relations and is not very flexible (if you add more models or modify model's field you'll need to update the code and redeploy the app). Using Metadata API (https://developers.google.com/appmaker/scripting/api/server#Metadata) can solve both issues, but it would not be so trivial to implement. — Pavel Shkleinik, Mar 01 '18 at 16:39
Hey cool thank you, but some questions:does exportData() function need to be called somewhere?where does the new spreadsheet will be created?
how to just export to current existing spreadsheet instead of creating a new one? — alep, Mar 02 '18 at 07:21
@MuhammadAlifBinAzali- The new spreadsheet will be created on the user's drive that invokes the function. Also, of course you have to invoke the exportData() function. That is why I shared the demo app so you can see it by yourself. You can also export to an existing sheet instead of creating a new one, but I'll leave that up to you. P.S. Contrary to what pavel believes, this can be adapted to work with relations too but he has a good point regarding model changes. — Morfinismo, Mar 02 '18 at 11:43
@Morfinismo yeah thanks a lot for making me understand. It working just fine. Thank you for your advise. Really appreciate it. — alep, Mar 06 '18 at 03:36

score 0 · Answer 2 · answered Mar 01 '18 at 16:47

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

Export data from Google AppMaker Datasource automatically

2 Answers2

Linked