How to create an automated gSheet backup within your app

integrations
references
(Levent Kulacoglu) #1

Dear valuable members of the AppSheet Community,

I would like to share and explain, how it’s possible to create an automatic backup feature for your app’s back-end. This post will cover the details of a “HOW-TO” and include:

  • Google Sheet Structuring
  • Google Drive Structuring
  • Google Apps Scripting
  • Google Apps Script - Installable Trigger Setting

Part 1 | Google Sheet Structuring


Step1: Add an extra tab to your back end and name it as you like i.e. Backup, Backup Settings etc.
Step2: Structure the sheet like this:


Column A - Header
Column B - ID //put ID001 as a default value
Column C - BACKUP
Column D - WIPE
Column E - DAYS
Column F - CALLBACK FUNCTION
Column G - INTERVAL
Column H - NR OF DAYS
Column I - NR OF WEEKS
Column J - DAY OF MONTH
Column K - WEEKDAY
Column L - TIME
Column M - TRIGGER ID
Column N - ACTIVATE?

Step3: Import this table to your app with ADDS_AND_UPDATES privilege and structure it as follows:

[HEADER]
{
	Type: Show
	Category: Section_Header
	Content: DATA BACKUP AND REMOVAL SETTINGS //or any other descriptive text you would like
}

[ID]
{
	Type: Text
	Key: YES
	Label: YES
	Initial Value: UNIQUEID()
	Required: YES
}

[BACKUP]
{
	Type: Enum
	Values: {"ON", "OFF"}
	AllowOtherValues: FALSE
	AutoCompleteOtherValues: TRUE
	BaseType: Text
	EnumInputMode: Buttons
	Display name: BACKUP UTILITY
}

[WIPE]
{
	Type: Enum,
	Values: {"ON", "OFF"}
	AllowOtherValues: FALSE
	AutoCompleteOtherValues: TRUE
	BaseType: Text
	EnumInputMode: Buttons
	Show_if: =ISNOTBLANK([BACKUP])
	Display name: =IFS(CONTEXT("ViewType")="Detail","WIPE OLD DATA?")
	Description: =CONCATENATE("DO YOU WANT OLD DATA TO BE WIPED OFF?")
}

[DAYS]
{
	Type: Number,
	Max Value: Integer; 30
	Min Value: Integer; 1
	Step Value: Integer; 1
	NumberDisplayMode: Range
	Show_if: =[WIPE]="YES"
	Required_if: =[WIPE]="YES"
	Reset_if: =[WIPE]="NO"
	Display name: =IFS(CONTEXT("ViewType")="Detail","FILTERING (Days)"),
	Description: =CONCATENATE("ENTER NUMBER OF CALENDAR DAYS OF DATA THAT YOU WANT TO PRESERVE IN THE BACK-END DATABASE","
","(Range from 1 to 30 days.)")
	Initial value: =7
}

[CALLBACK FUNCTION]
{
	Type: Text
	Show_if: =AND(CONTEXT("ViewType")="Detail",ISNOTBLANK([_THIS]))
	AppFormula: =IFS(
					[BACKUP]="ON",
					IFS(
						[WIPE]="YES","createBackupAndWipeData",
						[WIPE]="NO","createBackupOnly"
					),
					[BACKUP]="OFF",
					IFS(
						[WIPE]="YES","removeOldData",
						[WIPE]="NO","NO FUNCTIONS TO TRIGGER"
					)
				)
}

[INTERVAL]
{
	Type: Enum
	Values: {"DAILY", "WEEKLY", "MONTHLY"}
	AllowOtherValues: FALSE
	AutoCompleteOtherValues: TRUE
	BaseType: Text
	EnumInputMode: Buttons
	Show_if: =AND(
				NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
				ISNOTBLANK([WIPE])
			 )
	Required_if: =NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER")
	Display name: =EXECUTION INTERVAL
}

[NR OF DAYS]
{
	Type: Number,
	Max Value: Integer; 7
	Min Value: Integer; 1
	Step Value: Integer; 1
	NumberDisplayMode: Range
	Show_if: =AND(
				NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
				[INTERVAL]="DAILY"
			)
	Required_if: =AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="DAILY"
				)
	Reset_if: =NOT([INTERVAL]="DAILY")
	Display name: =IFS(CONTEXT("ViewType")="Detail","FREQUENCY (Days)"),
	Description: =CONCATENATE("ENTER THE EXECUTION FREQUENCY:","
				","(1 = Everyday, 2 = Bi-daily etc.)"
				)
}

[NR OF WEEKS]
{
	Type: Number,
	Max Value: Integer; 4
	Min Value: Integer; 1
	Step Value: Integer; 1
	NumberDisplayMode: Range
	Show_if: =AND(
				NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
				[INTERVAL]="WEEKLY"
			)
	Required_if: =AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="WEEKLY"
				)
	Reset_if: =NOT([INTERVAL]="WEEKLY")
	Display name: =IFS(CONTEXT("ViewType")="Detail","FREQUENCY (Weeks)"),
	Description: =CONCATENATE("ENTER THE EXECUTION FREQUENCY:","
				","(1 = Every Week, 2 = Bi-weekly etc.)"
				)
}

[DAY OF MONTH]
{
	Type: Number,
	Max Value: Integer; 30
	Min Value: Integer; 1
	Step Value: Integer; 1
	NumberDisplayMode: Range
	Show_if: =AND(
				NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
				[INTERVAL]="MONTHLY"
			)
	Required_if: =AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="MONTHLY"
				)
	Reset_if: =NOT([INTERVAL]="MONTHLY")
	Display name: =SELECT THE DAY OF MONTH FOR EXECUTION
}

[INTERVAL]
{
	Type: Enum,
	Values: {"SUNDAY", "MONDAY", "TUESDAY", "WEDNESDAY", "THURSDAY", "FRIDAY", "SATURDAY"}
	AllowOtherValues: FALSE
	AutoCompleteOtherValues: TRUE
	BaseType: Text
	EnumInputMode: Stack
	Show_if: =AND(
				NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
				[INTERVAL]="WEEKLY"
			),
	Required_if: =AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="WEEKLY"
				),
	Display name: =IFS(CONTEXT("ViewType")="Detail","EXECUTION WEEKDAY")
	Description: =SELECT A WEEKDAY FOR EXECUTION
}

[TIME]
{
	Type: Time,
	IgnoreSeconds: TRUE
	Show_if: =AND(
				NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
				AND(ISNOTBLANK([INTERVAL]),
					SWITCH(
						[INTERVAL],
						"DAILY",ISNOTBLANK([NR OF DAYS]),
						"WEEKLY",ISNOTBLANK([NR OF WEEKS]),
						ISNOTBLANK([DAY OF MONTH])
					)
				),
				IFS([INTERVAL]="WEEKLY",ISNOTBLANK([WEEKDAY]),TRUE,TRUE)
			)
	Required_if: =NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
	Display name: =IFS(CONTEXT("ViewType")="Detail","EXECUTION TIME"),
	Description: =CONCATENATE("SELECT A TIME FOR EXECUTION","
				","(Note: Execution will occur in plus or minus 15 minutes of the set time)"
				)
	Initial value: TIMENOW()
}

[TRIGGER ID]
{
	Type: Text
	Read-Only: ON
	Hidden: ON
}

[ACTIVATE?]
{
	Type: Yes/No
	Show_if: =IFS(
				AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="DAILY"
				,AND(ISNOTBLANK([NR OF DAYS]),ISNOTBLANK([TIME]),CONTEXT("ViewType")="Detail"),
				AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="WEEKLY"
				),AND(ISNOTBLANK([NR OF WEEKS]),ISNOTBLANK([WEEKDAY]),ISNOTBLANK([TIME]),CONTEXT("ViewType")="Detail"),
				AND(
					NOT([CALLBACK FUNCTION]="NO FUNCTIONS TO TRIGGER"),
					[INTERVAL]="MONTHLY"
				),AND(ISNOTBLANK([DAY OF MONTH]),ISNOTBLANK([TIME]),CONTEXT("ViewType")="Detail")
			)
	Display name: =IFS(
					ISBLANK([ACTIVATE?]),"YOUR BACKUP UTILITY IS NOT INITIALIZED YET. DO YOU WANT TO ACTIVATE IT NOW?",
					[ACTIVATE?]=FALSE,"YOUR BACKUP UTILITY IS DE-ACTIVATED. DO YOU WANT TO RE-ACTIVATE IT NOW?",
					[ACTIVATE?]=TRUE,"YOUR BACKUP UTILITY IS INITIALIZED. YOU CAN SWITCH-OFF TO DE-ACTIVATE THE UTILITY FROM HERE"
				)
}

[BACKUP_INFO] - *Virtual Column*
{
	Type: Show
	Category: Text
	Content: =IFS(
				AND(ISBLANK([ACTIVATE?]),ISBLANK([CALLBACK FUNCTION])),
				"YOUR DATA BACKUP & REMOVAL UTILITY HAS NOT BEEN INITIALIZED YET! CLICK ON THE GEAR ICON BELOW TO SET-UP THIS UTILITY. AFTER SAVING YOUR SETUP, YOU NEED TO ACTIVATE THE UTILITY FROM THIS PAGE.",
				AND(ISBLANK([ACTIVATE?]),ISNOTBLANK([CALLBACK FUNCTION])),
				"YOU HAVE SUCCESSFULLY SET-UP YOUR DATA BACKUP & REMOVAL UTILITY. TO INITIALIZE, PLEASE TURN ON THE ACTIVATION SWITCH ON THE BOTTOM OF THIS PAGE!",
				AND([ACTIVATE?]=TRUE,ISNOTBLANK([CALLBACK FUNCTION])),
				"YOUR DATA BACKUP & REMOVAL UTILITY IS NOW UP AND RUNNING!",
				AND([ACTIVATE?]=FALSE,ISNOTBLANK([CALLBACK FUNCTION])),
				"YOUR DATA BACKUP & REMOVAL UTILITY IS DE-ACTIVATED! TO RE-ACTIVATE, PLEASE TURN ON THE ACTIVATION SWITCH ON THE BOTTOM OF THIS PAGE!"
			)
	Show_if: =CONTEXT("ViewType")="Detail"
	AppFormula: =""
}

Step4: Add a UX View for this table with following settings:

{
	View name: Backup Settings" //or any name you see fit
	For this data: *Select your table*
	View type: detail
	Position: menu
	Quick edit columns: [ACTIVATE?]
	Column order: [HEADER]
				[BACKUP_INFO]
				[BACKUP]
				[WIPE]
				[DAYS]
				[CALLBACK FUNCTION]
				[INTERVAL]
				[NR OF DAYS]
				[NR OF WEEKS]
				[DAY OF MONTH]
				[WEEKDAY]
				[TIME]
				[ACTIVATE?]
	Display mode: Centered
	Show if: *you can set any expression if you want to show/hide this view from users/specific users*
}

Part 2 | Google Drive Structuring


Step1: Create a backup folder in your Google Drive either under the root or under any root folder as a subfolder.
Step2: Double click to open this folder and copy the Folder ID to a notepad, as we will need that later as shown in the image


Part 3 | Google App Scripting


Step1: Open your Google Sheet and from the menu choose Tools > Script Editor


The Script editor will by-default open with the Code.gs pane. Copy the below script code and paste in the pane. Save the project and assign a name to your script project.

var backupFolderId = "copy your backup folder ID here";
var sheetID = "copy your gSheet ID here";
var controlLimit = (SpreadsheetApp.openById(sheetID).getSheetByName("Settings").getRange("E2").getValue() !== "") ? Number(SpreadsheetApp.openById(sheetID).getSheetByName("Settings").getRange("E2").getValue()) : 0; //change the "Settings" to match with your given sheet name

// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onMySheetOpen(e) {
  var ss = e.source; //SpreadsheetApp.openById("sheetID"); //
  var menuEntries = [];
  var subMenuEntries = [];
  
  menuEntries.push({name: "⌚ Activate Backup", functionName: "setBackupTrigger"});
  menuEntries.push(null); // line separator
  menuEntries.push({name: "▶ Backup Only", functionName: "createBackupOnly"});
  menuEntries.push({name: "▶ Backup and Wipe", functionName: "createBackupAndWipeData"});
  menuEntries.push({name: "▶ Remove Old Data", functionName: "removeOldData"});

  ss.addMenu("BACKUP Menu", menuEntries);
}

function createBackupOnly() {
  var isActivated = isBackupActivated();
  if (isActivated == false) {
    SpreadsheetApp.getActiveSpreadsheet().toast("You shall activate the Backup Utility first, before running any command manually", "WARNING!", -1);
    return
  } else {
    var months = ["JAN" , "FEB" , "MAR" , "APR" , "MAY" , "JUN" , "JUL" , "JUL" , "AUG" , "SEP" , "OCT" , "DEC"];
    var backupDate = new Date().getDate() + months[new Date().getMonth()] + new Date().getFullYear();
    var fileName = backupDate + "_"+SpreadsheetApp.openById(sheetID).getName()+"_Backup";
    Logger.log("Backup Process initiated...");
    Logger.log("Backup Filename > "+fileName+".gSheet");
    DriveApp.getFileById(sheetID).makeCopy(fileName, DriveApp.getFolderById(backupFolderId));
  }
}

function createBackupAndWipeData() {
  var isActivated = isBackupActivated();
  if (isActivated == false) {
    SpreadsheetApp.getActiveSpreadsheet().toast("You shall activate the Backup Utility first, before running any command manually", "WARNING!", -1);
    return
  } else {
    var months = ["JAN" , "FEB" , "MAR" , "APR" , "MAY" , "JUN" , "JUL" , "JUL" , "AUG" , "SEP" , "OCT" , "DEC"];
    var backupDate = new Date().getDate() + months[new Date().getMonth()] + new Date().getFullYear();
    var fileName = backupDate + "_"+SpreadsheetApp.openById(sheetID).getName()+"_Backup";
    Logger.log("Backup Process initiated...");
    Logger.log("Backup Filename > "+fileName+".gSheet");
    DriveApp.getFileById(sheetID).makeCopy(fileName, DriveApp.getFolderById(backupFolderId));
    removeOldData();
  }
}

function removeOldData() {
  // The required code for removing/wiping old data can change from app to app, so it's not included here
}

function isBackupActivated() {
  var triggerSet = false;
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getEventType() == ScriptApp.EventType.ON_CHANGE) {
      if (triggers[i].getHandlerFunction() == "resetTrigger") {
        triggerSet = true;
        break
      }
    }
  }
  return triggerSet
}

Step2: Locate the File menu in the editor, choose File > New > Script file and name the new fie as Data_Backup_and_Removal
Step3: Copy and paste below GAS code this new file. Save the project.

function setBackupTrigger() {
  var sSht = SpreadsheetApp.openById(sheetID);
  ScriptApp.newTrigger("resetTrigger")
  .forSpreadsheet(sSht)
  .onChange()
  .create();
}

function resetTrigger(e) {
  var sSht = e.source;
  var sht = sSht.getActiveSheet();
  try {
    var shtName = sht.getName();
  } catch (e) {};
  
  if (shtName === "Settings") { //change the sheetname to fit your sheetname
    Logger.log("Sheet: "+shtName);
    var range = sSht.getActiveRange();
    Logger.log("Range: "+range.getA1Notation());
    var row = range.getRow();
    Logger.log("Active Row: "+row);
    var activateStatus = sht.getRange("N"+row).getValue();
    var triggerID = sht.getRange("M"+row).getValue();
    Logger.log("TriggerID: "+triggerID);
    
    if (activateStatus == "true") {
		var tname = sht.getRange("F"+row).getValue();
		var interval = sht.getRange("G"+row).getValue();
		var dayOfMonth = sht.getRange("J"+row).getValue();
		var nrOfDays = sht.getRange("H"+row).getValue();
		var nrOfWeeks = sht.getRange("I"+row).getValue();
		var wDay = sht.getRange("K"+row).getValue();
		var time = sht.getRange("L"+row).getValue();
		var hour = parseInt(time.getHours());
		var min = parseInt(time.getMinutes());
		
		if (tname === "NO FUNCTIONS TO TRIGGER") {
		  if (triggerID.length > 0) {
			Logger.log("There is an active trigger set...Deleting the active trigger...");
			var activeTriggers = ScriptApp.getProjectTriggers();
			
			if (activeTriggers.length > 0) {
			  for (var i = 0; i < activeTriggers.length; i++) {
				if (activeTriggers[i].getUniqueId() === triggerID) {
				  ScriptApp.deleteTrigger(activeTriggers[i]);
				  sht.getRange("M"+row).clearContent()
				}
			  }
			  Logger.log("Active trigger deleted!");
			}
		  }
		} else {
			if (triggerID.length == 0) {
			  Logger.log("No triggers have been set...Creating the trigger...")
			  
			  if (interval === "DAILY") {
				var myTrigger = ScriptApp.newTrigger(tname)
				.timeBased()
				.everyDays(nrOfDays)
				.atHour(hour)
				.nearMinute(min)
				.create();
				Logger.log("Run on every "+nrOfDays+" day(s)");
				Logger.log("atHour: "+hour);
				Logger.log("nearMinute: "+min);
				var id = myTrigger.getUniqueId();
				sht.getRange("M"+row).setValue(id);
				Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				
			  } else if (interval === "WEEKLY") {
				var myTrigger = ScriptApp.newTrigger(tname)
				.timeBased()
				.everyWeeks(nrOfWeeks)
				.onWeekDay(eval('ScriptApp.WeekDay.'+wDay))
				.atHour(hour)
				.nearMinute(min)
				.create();
				Logger.log("Run on every "+nrOfWeeks+" week(s)");
				Logger.log("on WeekDay: "+wDay);
				Logger.log("atHour: "+hour);
				Logger.log("nearMinute: "+min);
				var id = myTrigger.getUniqueId();
				sht.getRange("M"+row).setValue(id);
				Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				
			  } else if (interval === "MONTHLY") {
				var myTrigger = ScriptApp.newTrigger(tname)
				.timeBased()
				.onMonthDay(dayOfMonth)
				.atHour(hour)
				.nearMinute(min)
				.create();
				Logger.log("Run on day "+dayOfMonth+" of each month");
				Logger.log("atHour: "+hour);
				Logger.log("nearMinute: "+min);
				var id = myTrigger.getUniqueId();
				sht.getRange("M"+row).setValue(id);
				Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
			  }
			  
			} else if (triggerID !== "" || triggerID != null) {
			  Logger.log("There is an active trigger set...Deleting the active trigger...");
			  var activeTriggers = ScriptApp.getProjectTriggers();
			  
			  if (activeTriggers.length > 0) {
				for (var i = 0; i < activeTriggers.length; i++) {
				  if (activeTriggers[i].getUniqueId() === triggerID) {
					ScriptApp.deleteTrigger(activeTriggers[i]);
				  }
				}
				Logger.log("Active trigger deleted...Setting the new trigger...");
				if (interval === "DAILY") {
				  var myTrigger = ScriptApp.newTrigger(tname)
				  .timeBased()
				  .everyDays(nrOfDays)
				  .atHour(hour)
				  .nearMinute(min)
				  .create();
				  Logger.log("Run on every "+nrOfDays+" day(s)");
				  Logger.log("atHour: "+hour);
				  Logger.log("nearMinute: "+min);
				  var id = myTrigger.getUniqueId();
				  sht.getRange("M"+row).setValue(id);
				  Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				  
				} else if (interval === "WEEKLY") {
				  var myTrigger = ScriptApp.newTrigger(tname)
				  .timeBased()
				  .everyWeeks(nrOfWeeks)
				  .onWeekDay(eval('ScriptApp.WeekDay.'+wDay))
				  .atHour(hour)
				  .nearMinute(min)
				  .create();
				  Logger.log("Run on every "+nrOfWeeks+" week(s)");
				  Logger.log("on WeekDay: "+wDay);
				  Logger.log("atHour: "+hour);
				  Logger.log("nearMinute: "+min);
				  var id = myTrigger.getUniqueId();
				  sht.getRange("M"+row).setValue(id);
				  Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				  
				} else if (interval === "MONTHLY") {
				  var myTrigger = ScriptApp.newTrigger(tname)
				  .timeBased()
				  .onMonthDay(dayOfMonth)
				  .atHour(hour)
				  .nearMinute(min)
				  .create();
				  Logger.log("Run on day "+dayOfMonth+" of each month");
				  Logger.log("atHour: "+hour);
				  Logger.log("nearMinute: "+min);
				  var id = myTrigger.getUniqueId();
				  sht.getRange("M"+row).setValue(id);
				  Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				}
				
			  } else {
				if (interval === "DAILY") {
				  var myTrigger = ScriptApp.newTrigger(tname)
				  .timeBased()
				  .everyDays(nrOfDays)
				  .atHour(hour)
				  .nearMinute(min)
				  .create();
				  Logger.log("Run on every "+nrOfDays+" day(s)");
				  Logger.log("atHour: "+hour);
				  Logger.log("nearMinute: "+min);
				  var id = myTrigger.getUniqueId();
				  sht.getRange("M"+row).setValue(id);
				  Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				  
				} else if (interval === "WEEKLY") {
				  var myTrigger = ScriptApp.newTrigger(tname)
				  .timeBased()
				  .everyWeeks(nrOfWeeks)
				  .onWeekDay(eval('ScriptApp.WeekDay.'+wDay))
				  .atHour(hour)
				  .nearMinute(min)
				  .create();
				  Logger.log("Run on every "+nrOfWeeks+" week(s)");
				  Logger.log("on WeekDay: "+wDay);
				  Logger.log("atHour: "+hour);
				  Logger.log("nearMinute: "+min);
				  var id = myTrigger.getUniqueId();
				  sht.getRange("M"+row).setValue(id);
				  Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				  
				} else if (interval === "MONTHLY") {
				  var myTrigger = ScriptApp.newTrigger(tname)
				  .timeBased()
				  .onMonthDay(dayOfMonth)
				  .atHour(hour)
				  .nearMinute(min)
				  .create();
				  Logger.log("Run on day "+dayOfMonth+" of each month");
				  Logger.log("atHour: "+hour);
				  Logger.log("nearMinute: "+min);
				  var id = myTrigger.getUniqueId();
				  sht.getRange("M"+row).setValue(id);
				  Logger.log("Trigger has been set for "+tname+" | Trigger ID: "+id);
				}
			  }
			}
		}
	} else if (activateStatus == "false" || activateStatus == "" || activateStatus == null) {
      
	  if (triggerID.length > 0) {
		Logger.log("There is an active trigger set...Deleting the active trigger...");
		var activeTriggers = ScriptApp.getProjectTriggers();
			
		if (activeTriggers.length > 0) {
          for (var i = 0; i < activeTriggers.length; i++) {
			if (activeTriggers[i].getUniqueId() === triggerID) {
              ScriptApp.deleteTrigger(activeTriggers[i]);
              sht.getRange("M"+row).clearContent()
			}
          }
          Logger.log("Active trigger deleted!");
		}
      }
	}
  }
}


Part 4 | Google App Script - Installable Trigger Setting


To activate the backup feature, you have 2 options to initiate it:
Option#1
Close your gSheet and re-open it OR refresh your browser window (Ctrl+Shift+R). You will notice the BACKUP Menu item next to Help menu on the upper tier of the Google Sheet. Click on it and choose Activate Backup
image

Option#2
In the script editor, locate Run menu item, choose Run function and then choose setBackupTrigger as shown in the image

After you run above function it will set an onChange(e) trigger for your Google Sheet, which will capture any change in the designated/set sheet (the name that you have given) changed by the your AppSheet app and set an Installable Trigger which will automatically run in the defined intervals and settings.


SUMMARY | Setup your backup


As we are all set now, just launch your app, go to your <BackUp View>, edit your record and set your backup as you see fit and save the record. After you save the record, you should explicitly set the ACTIVATION toggle to ON at the end of the view. With that set to ON, the script will automatically catch the changes in that particular sheet, setup an installable time trigger based on your settings and your detail view will look like pretty much like this:

Hope it will be helpful to anyone.

Cheers…

12 Likes
Sending Notifications to all registers app users?