Create automated backups with Apps Script

Sheet to sheet data backup

Introduction

As we know, working with Google Apps Script is working with quotas, limits and with adaptation to the changes and updates Google launch constantly. In this post I share with you a perfect use-case of Apps Script in diary workflow; create backups with Apps Script.

Imagine you have a google sheet that receives 300+ entries each day. It can be from a Google From, Web App or even Appsheet.

About 363 records each day

The problem comes when you have to load that info in your app. For example when load your app I’m sure you have seen this screenshot in your devices. The more the data, the more time to load. ⏳

Appsheet loading

If you don’t have to see the records in the app. Or in case the users are allowed to delete records. You could use Apps Script to schedule backups of your data. Lets get hands on work.

Step 1

Create the backup sheet. Add the same headers that are in your app. Optionally, you can add one more header to add the backup date to each record.

Headers in your production app.

Headers for Appsheet’s spreadsheet

Headers in your backup sheet. One extra header column.

Headers for Backup spreadsheet

Step 2

Create a stand alone script. You can create it with the shortcut script.new in your chrome browser.

Get access to the spreadsheets

Remove the existing code and add two global constants.

const WORKSHEET_ID = 'YOUR_APP SPREADSHEET_ID';
const WORKSHEET_BAKCUP_ID = 'YOUR_BACKUP SPREADSHEET_ID';

Step 3

Create a function to paste the data in the backup sheet. The pasteInfo function open the backup Spreadsheet, then it access to the Backup sheet. It add rows to insert the data starting from row 2. Get the range with the rows recently added. Paste the info. Insert the backup date in the next column in each record.

function pasteInfo( data ){
   const ss = SpreadsheetApp.openById(WORKSHEET_BAKCUP_ID);
   const sheetBackup = ss.getSheetByName('Backup');
   // Add rows to insert the most recent backup in row 2.
   sheetBackup.insertRows(2,data.length);
    sheetBackup.getRange(2,1,data.length,data[0].length).setValues(data);
 sheetBackup.getRange(2,data[0].length+1,data.length,1).setValue([new Date()]);
   return true;
}

Now that you have the pasteInfo function ready. You can invoke this function at any point in your script. Let’s get to the next step.

Step 4

We need a function to get the data (if any) in the first spreadsheet.

The selectDataForBackUp works with the following logic.

1. Opens the application spreadsheet.
2. Get the sheet with the records.
3. Dynamically get the data (if any) in the sheet.
4. If there is data to backup. Make a call to the PasteInfo function
5. If the backup was successful, clean the records in the application sheet.
6. If there is no data to backup. Return a message.

function selectDataForBackUp(){
   const ss = SpreadsheetApp.openById(WORKSHEET_ID);
   const sheetRegistros = ss.getSheetByName('Registros');
   const [headers, ...data] =           sheetRegistros.getDataRange().getValues();
   /*   console.log(headers);
   console.log(data); */
   if( data.length > 0 ){
     const result = pasteInfo(data);
     if( result === true ){
       console.log('Clean the sheet');
  sheetRegistros.getRange(2,1,sheetRegistros.getLastRow()-1,sheetRegistros.getLastColumn()+1).clearContent();
}else{
    return "There are no records to add.";
    }
  }
}

This is how the script works. 🧑🏽‍💻

Bonus

Maybe you’re asking yourself. Man do I have to press the button to make the backup each time? No with Apps Script. You can create a Trigger to schedule the backups.

You can schedule your backups according to your necessities. It can be diary, weekly, monthly, etc.,

If you liked this tutorial, you can invite me a coffe ☕️ wich helps me to continue creating content.

Wrap

Now you know how to take data from one spreadsheet to another. You can modify and personalize it. If you like to see how to build this script step by step, I let you the Youtube tutorial bellow (spanish).