How to create an automated gSheet backup within your app

[LeventK]

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

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…

[Guiotto_Leal_Pr]

How to deal with this error:
Invalid value (line 3, file “Code”)

line 3:
var controlLimit = (SpreadsheetApp.openById(sheetID).getSheetByName(“backup”).getRange(“E2”).getValue() !== “”) ? Number(SpreadsheetApp.openById(sheetID).getSheetByName(“backup”).getRange(“E2”).getValue()) : 0; //change the “Settings” to match with your given sheet name

[LeventK]

@Guiotto_Leal_Pretti
Hard to say anything unless I see the whole code.

[Guiotto_Leal_Pr]

var backupFolderId = “1Y8UqVRWh3RgcqyLwnnHEggQhOLjGI60r”;
var sheetID = “edit#id=684951795”;
var controlLimit = (SpreadsheetApp.openById(sheetID).getSheetByName(“Kapital Manager”).getRange(“E2”).getValue() !== “”) ? Number(SpreadsheetApp.openById(sheetID).getSheetByName(“Kapital Manager”).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
}

[Guiotto_Leal_Pr]

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 === “Kapital Manager”) { //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!");
	}
  }
}

}
}

[LeventK]

Guiotto_Leal_Pretti:

var sheetID = “edit#id=684951795”;

The value of sheetID variable is not correct…When you take a look at the URI of the gSheet, the ID of a gSheet is the unique id marked with the red rectangle below:

https://docs.google.com/spreadsheets/d/gSheetID/edit?pli=1#gid=1378868853

[Guiotto_Leal_Pr]

Now or error changed:

TypeError: Unable to call “getRange” method null. (line 3, file “Code”)

thanks for help

[LeventK]

@Guiotto_Leal_Pretti
You have possibly mis-typed the code in line 3 or mis-typed your gSheet’s unique id because the error indicates that it cannot open the sheet. That line shall be like this:

var sheetID = "1zebFoBm4zhHAXvJoG4WNT3UbABAAK0X0W_ZdCHJ_png"

The ID between the double quotes is the ID that I have mentioned in my previous post

[Guiotto_Leal_Pr]

thank you.

[Pooja2]

hey u got answer im geeting same erorr please help me

[TotalSolutions]

There is an alternative to this I found on the web which I am using. Its a different way of backup but it works! Hope it helps you as well.

gist.github.com

https://gist.github.com/abhijeetchopra/99a11fb6016a70287112

0-README.md

# How to "Schedule Automatic Backups" of your Google Sheets

**This tutorial demonstrates how to use Google Apps Script to:**

* Create copies of the Google Sheet in the desired destination folder automatically at set intervals.
  
* Append the time stamp with each backup file's name.

* Adjust time trigger for backing up every day/hour/minute.

1-Config.md

1. Open your Google Drive and create a new folder right where your Google Sheet is. You may name it anything you like.
![step 1](http://i.imgur.com/TVNhCs3.jpg)  

2. Open the folder you just created. You will be able to see its ID in the URL displayed in your browser's address bar. We will need this later.
![step 2](http://i.imgur.com/pfH12nu.jpg)

3. Open your Google Sheet and select menu item "Tools" --> "Script Editor"
![step 3](http://i.imgur.com/mmrC8GZ.jpg)

4. Once the script editor opens, copy and paste the code from the **2-MakeCopy.gs** file (see file below) into the "Code.gs" file, replacing the code that's already included.

This file has been truncated. show original

2-MakeCopy.gs

// Abhijeet Chopra
// 26 February 2016
// Google Apps Script to make copies of Google Sheet in specified destination folder

function makeCopy() {

// generates the timestamp and stores in variable formattedDate as year-month-date hour-minute-second
var formattedDate = Utilities.formatDate(new Date(), "GMT", "yyyy-MM-dd' 'HH:mm:ss");

// gets the name of the original file and appends the word "copy" followed by the timestamp stored in formattedDate

This file has been truncated. show original

[Pooja2]

Thank You

[Lucinda_Mason]

That’s what I’ve been using for a while and it works great.

[adiboo_adib]

i have some questions
1 i have this error :
var backupFolderId = “1d8BWQ-0TW64s6ZzDHc3KWb_zFP1kcCTI”;
var sheetID = “1LLTB0T738qT3rYlzFhVQhl2rf_YOg5lTD9hhegVMWoM”;
var controlLimit = (SpreadsheetApp.openById(1LLTB0T738qT3rYlzFhVQhl2rf_YOg5lTD9hhegVMWoM).getSheetByName(“Ressources H”).getRange(“E2”).getValue() !== “”) ? Number(SpreadsheetApp.openById(1LLTB0T738qT3rYlzFhVQhl2rf_YOg5lTD9hhegVMWoM).getSheetByName(“Ressources H”).getRange(“E2”).getValue()) : 0; //change the “Settings” to match with your given sheet name

2- i have also this error

Column Name ‘ACTIVATE?’ in Schema ‘Backup_Schema’ of Column Type ‘Yes/No’ has an invalid data validation constraint ‘=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”) )’. Expression ‘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 M…’ could not be parsed due to exception: Number of opened and closed parentheses does not match.

3- on step 3 you mention [Interval] twice

Thanks

[LeventK]

adiboo_adib:

i have some questions
1 i have this error :
var backupFolderId = “1d8BWQ-0TW64s6ZzDHc3KWb_zFP1kcCTI”;
var sheetID = “1LLTB0T738qT3rYlzFhVQhl2rf_YOg5lTD9hhegVMWoM”;
var controlLimit = (SpreadsheetApp.openById(1LLTB0T738qT3rYlzFhVQhl2rf_YOg5lTD9hhegVMWoM).getSheetByName(“Ressources H”).getRange(“E2”).getValue() !== “”) ? Number(SpreadsheetApp.openById(1LLTB0T738qT3rYlzFhVQhl2rf_YOg5lTD9hhegVMWoM).getSheetByName(“Ressources H”).getRange(“E2”).getValue()) : 0; //change the “Settings” to match with your given sheet name

What’s the error or error message?

adiboo_adib:

2- i have also this error

Column Name ‘ACTIVATE?’ in Schema ‘Backup_Schema’ of Column Type ‘Yes/No’ has an invalid data validation constraint ‘=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”) )’. Expression ‘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 M…’ could not be parsed due to exception: Number of opened and closed parentheses does not match.

Points out to an unmatching parentheses problem. You can figure it out I believe.

adiboo_adib:

3- on step 3 you mention [Interval] twice

What’s or where’s the problem? Couldn’t be able to understand.

[adiboo_adib]

Resolved thanks

[LeventK]

Excellent

[WilsonWee]

Hi Levent, I know you may get this a lot but I’m still getting this error

“TypeError: Cannot read property ‘getRange’ of null (line 3, file “backup”)”

Could you help me with my code below?

var backupFolderId = “1ITCw5ODT3MwUsOzgIiLwq5jgJu0bcu3X”;
var sheetID = “1QQa1kSxkEug7t9jK_PhQCiy3bDGdwHAigG9wFvWbvOI”;
var controlLimit = (SpreadsheetApp.openById(sheetID).getSheetByName(“PESbackup”).getRange(“E2”).getValue() !== “”) ? Number(SpreadsheetApp.openById(sheetID).getSheetByName(“PESbackup”).getRange(“E2”).getValue()) : 0; //change the “Settings” to match with your given sheet name

Many thanks in advance.

[Ratatosk]

Hi Wilson, please be aware that you are showing the document id’s openly.

[LeventK]

@WilsonWee
What value do you have in E2 cell of your sheet?

[WilsonWee]

According to the Part 1 of the sheet structure as per your steps, it is in empty cell right now. A new tab is created within my database Gsheet.

A new tab is created in the Gsheet.

[LeventK]

@WilsonWee
The sheetname that you have specified in the script code (.getSheetByName(" PESbackup "), does not match with the actual sheet name in your gSheet in your screenshot (Backup). Either match the sheet name with the code or match the code with the sheetname.

[WilsonWee]

Thank you sir. I misunderstood the instructions. Unfortunately, I’m faced with another error.

TypeError: Cannot read property "source" from undefined. (line 7, file "backup")

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

[LeventK]

Try with changing this line;

var ss = e.source; //SpreadsheetApp.openById(“sheetID”); //

With this

var ss = SpreadsheetApp.openById(“sheetID”); //sheetID is your gSheet ID as explained in detail thru the post

[WilsonWee]

Problem solved and I am grateful.

[LeventK]

Excellent

[TotalSolutions]

I am getting this error: IFS function is used incorrectly:Inputs to IFS() must be one or more condition-value pairs.

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

Please Help! Thank You!