Copying data between GSheets with AppScript

In our team, we have a lot of Google Sheets 📃 and we often need to show the same content between sheets. (e.g. 2 Sheets both need data in tab Average Daily Order. One is for internal analysis, another is a report for leaders.) In this post, I’d like to share some common solutions to this problem instead of manually copy-pasting data (That’s too time-consuming…)

importrange
AppScript: getValues + setValues
AppScript: batchGet + batchUpdate

Our Goal

Displaying the same data in Original Data’s transaction tab to Backup Data

ImportRange

In most cases, importRange will work just fine. (FYI, here is a nice article sharing solutions to most importRange errors )

=IMPORTRANGE("url_of_original_sheet","transaction!A:N")

Conditional Import

If we want to import specific columns or data fitting certain conditions, QUERY is a handy tool

=QUERY(IMPORTRANGE("url_of_original_sheet","transaction!A:N"), "select Col1, Col3, Col7")

import specific columns

=QUERY(IMPORTRANGE("url_of_original_sheet","transaction!A:N"), "select * where Col3='CC Payment'")

only import data meeting certain criteria

But when there is too many formula/data or having too many people editing the original sheet, importRange may not work 😥😕🤒 If stability is highly emphasized, we need to use AppScript 💎

AppScript

With AppScript, we can get data in one sheet, paste it in another sheet, then use trigger to specify its update frequency.

Pros: Backup Sheet is *always alive* ✨
Cons: Backup Sheet may not be real-time updated (Depends on how we set our trigger. We can notify ourselves when update fails 😉)

Let’s open the AppScript Project in our Original Sheet and get started

Method getValues + setValues

In Code.gs, paste and modify the following code

function Backup()
{
    let sheet = SpreadsheetApp.getActiveSpreadsheet()
                                     .getSheetByName("transaction");
    const backupSheetUrl = 'https://docs.google.com/spreadsheets/d
               /1SSHx4RiytJoV3X6EgtSb0bNow1YIt8txfrllEIZF9Pc/edit';
    
    // `Sheet2` is where we want to paste our Original Data
    let backupSheet = SpreadsheetApp.openByUrl(backupSheetUrl)
                                    .getSheetByName("Sheet2");
    let values = sheet.getDataRange().getValues();
    
    // Clear data but keep format in backupSheet
    // you can use `backupSheet.clear()` to clear both data and format
    backupSheet.clearContents();
    
    // paste data into Backup Sheet
    backupSheet.getRange(1,1,values.length,values[0].length)
                                        .setValues(values);
}

Now save and click Run to see if it works

After execution completes, Sheet2 in Backup Data is then updated :-)

Let’s create a trigger to execute this function automatically ⏲ (Personally, I do not recommand setting triggers to run less than every 15 minutes. In the code above, we clear all data and paste data afterwards, so you may see data suddenly disappear for 0.5 seconds each time when running Backup function.)

Method batchGet + batchUpdate

Method 1 is good enough for most cases. However, when we are dealing with large data, setValues could become extremely slow and even timedout (AppScript limits each execution to run 6 minutes maximum.) Another reason to use batchGet/Update is when the data range is not continuous, method 2 will be a better choice

We need to add Google Sheet API first:

Services → ➕ → Google Sheet API → Add

More usages of batchGet/Update can be found here.

function batchUpdate()
{
 // https://docs.google.com/spreadsheets/d/{this_is_Sheet_id}/edit#gid=0
    let originalSheetId="18vPr_Kghkv16gFp0KBptzY3yu8J4fp6b9NobppeHMWY";
    let backupSheetId="1SSHx4RiytJoV3X6EgtSb0bNow1YIt8txfrllEIZF9Pc";
    let sheet = SpreadsheetApp.openById(originalSheetId)
                        .getSheetByName("transaction");
    let rowNum = sheet.getLastRow();
    
    // get data in certain range from original sheet
    let response = Sheets.Spreadsheets.Values.batchGet(
        originalSheetId,{ranges: ['transaction!A1:N'+rowNum]});
    let values = response.valueRanges[0].values;
    
    // If your data range is not continuous, your batchGet range will 
    be:
    // ranges: ['transaction!A1:C' + rowNum, 'transaction!F1:N' + 
    rowNum, ...]
    // response.valueRanges[n].values is the corresponding values of 
    each range
    
    // specify data range and new values we want to set in Backup Sheet
    let request=
    {
        'valueInputOption': 'USER_ENTERED',
        'data': [
        {
            'range': 'Sheet3!A1:N'+values.length,
            'majorDimension': 'ROWS',
            'values': values
         }]
     };
     
     // Again, if the range to update is not continuous, `data` in the 
     request above will be:
     // 'data': [{'range': 'Sheet3!A1:C' + values1.length, 
     'majorDimension': 'ROWS', 'values': values1},
     //          {'range': 'Sheet3!F1:N' + values2.length, 
     'majorDimension': 'ROWS', 'values': values2}, ...]
     
     SpreadsheetApp.openById(backupSheetId).getSheetByName("Sheet3")
                                     .getDataRange().clearContent();
     
     // flush() will force gsheet to apply all pending changes.
     // If not added, sometimes update values will run before 
     clearContent :(
     SpreadsheetApp.flush();
     Sheets.Spreadsheets.Values.batchUpdate(request, backupSheetId);
}

Save and run the new function, we can see Sheet3 in Backup Data is now updated :D

We did not use copyTo in the example above because our task is to copy data to *different* GSheet. If you only need to copy-paste data within the same Gsheet, copyTo will do it for you

Source: Medium - Wendee

The Tech Platform