Copying data between GSheets with App Script

In collaborative environments, efficient data management across multiple Google Sheets is paramount. Whether it's sharing information among team members or generating reports for various stakeholders, streamlining data transfer processes is crucial. In this article, we'll explore the intricacies of copying data between Google Sheets using Google App Script, offering solutions beyond manual copy-pasting to save time and ensure accuracy.

Common Challenges:

Within a team, numerous scenarios arise where identical data must be displayed across multiple Google Sheets. For instance, consider a situation where two sheets necessitate the same dataset – one for internal analysis and the other for executive reporting. Manual copy-pasting becomes impractical and time-consuming, especially when dealing with large datasets or frequent updates.

Solutions:

To address these challenges effectively, let's explore two primary methods:

1. Using the IMPORTRANGE function for basic data import.

2. Using Google App Script for more advanced automation and customization.

1. Using the IMPORTRANGE function for Basic Data Import

Using the IMPORTRANGE function for basic data import in Google Sheets offers a convenient way to bring data from one sheet into another. This function is particularly useful for tasks where you need to aggregate data from multiple sources or share information between different sheets.

In case, IMPORTRANGE Error Occurs

IMPORTRANGE allows you to import data from a specified range of cells in one Google Sheet to another. The syntax of the function is simple:

=IMPORTRANGE("url_of_original_sheet", "range_to_import")

• "url_of_original_sheet" is the URL of the Google Sheet containing the data you want to import.

• "range_to_import" specifies the range of cells from which you want to import data.

For example, if you have a sheet named "transaction" in the original sheet and you want to import columns A to N, the formula would look like this:

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

Conditional Import:

One limitation of the IMPORTRANGE function is that it imports all data from the specified range without any filtering. However, you can overcome this limitation by using the QUERY function in conjunction with IMPORTRANGE to perform conditional imports.

Here's how it works:

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

This QUERY formula imports only columns 1, 3, and 7 from the "transaction" tab of the original sheet.

You can further refine the import by adding conditions. For example, to import only rows where the value in column 3 is "CC Payment", you can use:

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

This will only import data meeting the specified criteria.

Limitations of IMPORTRANGE:

While IMPORTRANGE is suitable for most basic data import tasks, it may encounter limitations in certain scenarios:

• Complex Formulas: If the original sheet contains complex formulas or functions, IMPORTRANGE may not handle them correctly, resulting in errors or unexpected behavior.

• Extensive Data: Importing large amounts of data using IMPORTRANGE can sometimes lead to performance issues, especially if the data range is substantial.

• Simultaneous Editing: When multiple users are editing the original sheet simultaneously, it can cause conflicts or delays in data import using IMPORTRANGE.

Consideration for Stability:

In situations where stability and reliability are paramount, especially with complex datasets or multiple users editing the original sheet, using Google App Script becomes a viable alternative. App Script allows for more control over the data import process and can handle more complex scenarios with ease.

App Script

Google App Script is a powerful scripting platform provided by Google to automate tasks within Google Sheets. By writing custom scripts using JavaScript, users can perform various operations, including fetching data from one sheet and pasting it into another. This offers more control and reliability compared to using the IMPORTRANGE function.

Method 1: getValues + setValues:

This method involves using the getValues and setValues functions in Google App Script to transfer data between sheets.

Here's how it works:

1. Fetching Data: The getValues method is used to fetch data from the original sheet. It retrieves the values from the entire range of cells in the specified sheet.

2. Pasting Data: The fetched data is then pasted into the target sheet using the setValues method. This method requires specifying the range where the data should be pasted.

Here's an example script:

function Backup() {
    // Access the active spreadsheet and target sheet by name
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("transaction");
    const backupSheetUrl = 'https://docs.google.com/spreadsheets/d/1SSHx4RiytJoV3X6EgtSb0bNow1YIt8txfrllEIZF9Pc/edit';
    let backupSheet = SpreadsheetApp.openByUrl(backupSheetUrl).getSheetByName("Sheet2");
    
    // Fetch all values from the original sheet
    let values = sheet.getDataRange().getValues();
    
    // Clear existing content in the backup sheet
    backupSheet.clearContents();
    
    // Paste fetched values into the backup sheet
    backupSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

Pros:

• Provides more control over the data transfer process.

• Backup sheet is always updated with the latest data.

Cons:

• Real-time updates may not be possible, depending on how triggers are set.

• May encounter performance issues with large datasets.

Method 2: batchGet + batchUpdate:

This method is more suitable for handling large datasets or scenarios where real-time updates are crucial. It involves using the Google Sheets API to fetch and update data in batches.

Here's how it works:

1. Fetching Data: The batchGet method retrieves data from the original sheet in batches.

2. Updating Data: The fetched data is then updated in the target sheet using the batchUpdate method.

Here's an example script:

function batchUpdate() {
    // Define original and backup sheet IDs
    let originalSheetId = "18vPr_Kghkv16gFp0KBptzY3yu8J4fp6b9NobppeHMWY";
    let backupSheetId = "1SSHx4RiytJoV3X6EgtSb0bNow1YIt8txfrllEIZF9Pc";
    
    // Access the original sheet and determine its last row
    let sheet = SpreadsheetApp.openById(originalSheetId).getSheetByName("transaction");
    let rowNum = sheet.getLastRow();
    
    // Fetch data in a specific range from the original sheet
    let response = Sheets.Spreadsheets.Values.batchGet(originalSheetId, {
        ranges: ['transaction!A1:N' + rowNum]
    });
    let values = response.valueRanges[0].values;
    
    // Prepare the request to update the backup sheet
    let request = {
        'valueInputOption': 'USER_ENTERED',
        'data': [{
            'range': 'Sheet3!A1:N' + values.length,
            'majorDimension': 'ROWS',
            'values': values
        }]
    };
    
    // Clear existing content in the backup sheet
    SpreadsheetApp.openById(backupSheetId).getSheetByName("Sheet3").getDataRange().clearContent();
    
    // Apply pending changes and update the backup sheet
    SpreadsheetApp.flush();
    Sheets.Spreadsheets.Values.batchUpdate(request, backupSheetId);
}

Now save and click Run to see if it works

Pros:

• Efficient handling of large datasets.

• Suitable for non-continuous data ranges.

• Enhanced performance compared to setValues.

Cons:

• Requires setup of Google Sheets API.

• Execution time is limited to 6 minutes.

• May require additional configuration for complex scenarios.

Setting up Trigger for Automated Data Transfer

Creating triggers in Google Apps Script allows for the automation of script execution at specified intervals, providing a convenient way to schedule tasks such as data backups, updates, or any other repetitive operations. Let's delve into the details of how triggers work and how they can be utilized to automate the execution of the Backup function mentioned earlier.

How Triggers Work:

Triggers in Google Apps Script are essentially time-driven events that can be set to execute a specified function at predetermined intervals. These intervals can range from minutes to weeks, offering flexibility in scheduling automated tasks.

When a trigger is created for a function, Google's servers automatically run the function at the specified time intervals, even if the associated spreadsheet is not open or the user is not actively using it. This ensures that tasks are executed reliably and consistently without manual intervention.

Setting Up Triggers for the Backup Function:

In our case, we want to automate the execution of the Backup function, which copies data from one sheet to another. To do this, we'll create a trigger that runs the Backup function at regular intervals.

Steps to Create a Trigger:

STEP 1: Open the Google Sheets spreadsheet containing the script. Go to Extensions > Apps Script to open the Script Editor.

If the Backup function is not already added, paste it into the Script Editor.

STEP 2: In the App Script Editor, go to Triggers in the left panel. Click on the Add Trigger button at the bottom right corner.

Configuring the Trigger:

• Select the function you want to trigger (Backup in our case) from the dropdown menu.

• Choose the event type (Time-driven).

• Specify the type of time-driven trigger (Day timer, Week timer, etc.).

• Set the interval at which the trigger should run (e.g., every day, every hour, every minute).

STEP 3: Click Save to create the trigger.

Benefits of Using Triggers:

• Automation: Triggers automate repetitive tasks, saving time and effort.

• Consistency: Tasks are executed at regular intervals, ensuring data backups or updates occur reliably.

• Reliability: Triggers run on Google's servers, even if the associated spreadsheet is not actively being used.

• Customization: Triggers can be configured to run at specific times and intervals, providing flexibility in scheduling.

Conclusion

Efficiently copying data between Google Sheets is essential for maintaining data consistency and streamlining workflows in collaborative environments. While IMPORTRANGE provides a convenient solution for basic imports, Google App Script offers greater customization and automation capabilities, making it ideal for complex data transfer tasks. By choosing the right method based on the specific requirements of your project, you can ensure seamless data synchronization across multiple Google Sheets.