📋 Copy Google Spreadsheet Data to another Sheet with Apps Script

1. Overview

\[ \begin{array}{l} \textbf{Google Apps Script allows automation of tasks within Google Sheets --} \\ \text{including copying data from one sheet to another programmatically.} \\ \textbf{This is ideal for backing up data, creating logs, or generating reports --} \\ \text{with minimal user interaction and efficient script execution.} \end{array} \]

2. Use Cases

            
                - Automatically archive or backup a sheet’s content to another tab
                - Copy form responses or logs to a master sheet
                - Filter and copy specific data based on conditions
                - Copy data between spreadsheets (not just sheets)
            
        

These tasks are frequently used in project management, data analysis workflows, and custom dashboards built on top of Google Sheets.

3. Sample Script to Copy All Data to Another Sheet

            
                function copySheetData() {
                    var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet
                    var sourceSheet = ss.getSheetByName("Sheet1");  // Source sheet
                    var targetSheet = ss.getSheetByName("Sheet2");  // Target sheet
                    
                    var data = sourceSheet.getDataRange().getValues(); // Get all data from source
                    targetSheet.clearContents(); // Optional: clear existing data in target
                    targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data); // Paste data
                }
            
        

This script copies the entire content of `Sheet1` into `Sheet2`, replacing existing data.

4. Explanation of the Code

            
                - SpreadsheetApp.getActiveSpreadsheet(): gets the active spreadsheet context
                - getSheetByName("Sheet1"): references the source sheet
                - getDataRange().getValues(): fetches all cells with content from the sheet
                - clearContents(): clears the target sheet before pasting new data
                - getRange(...).setValues(...): writes the data to the target sheet
            
        

The `getRange` method ensures the data is pasted starting from cell A1 and matches the dimensions of the original data exactly.

5. Copy Data Between Two Different Spreadsheets

            
                function copyBetweenSpreadsheets() {
                    var sourceSS = SpreadsheetApp.openById("SOURCE_SPREADSHEET_ID");
                    var targetSS = SpreadsheetApp.openById("TARGET_SPREADSHEET_ID");
                    var sourceSheet = sourceSS.getSheetByName("Sheet1");
                    var targetSheet = targetSS.getSheetByName("Sheet1");
                    
                    var data = sourceSheet.getDataRange().getValues();
                    targetSheet.clearContents();
                    targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
                }
            
        

Use `openById()` to access spreadsheets you own or have permission to access. This enables powerful cross-document automation.

6. Conditional Copy Example (Only Rows with "Approved")

            
                function copyApprovedRows() {
                    var ss = SpreadsheetApp.getActiveSpreadsheet();
                    var sourceSheet = ss.getSheetByName("Sheet1");
                    var targetSheet = ss.getSheetByName("ApprovedOnly");
                    var data = sourceSheet.getDataRange().getValues();
                    
                    var filtered = data.filter(function(row, index) {
                        return index === 0 || row[2] === "Approved"; // assuming status is in 3rd column
                    });
                    
                    targetSheet.clearContents();
                    targetSheet.getRange(1, 1, filtered.length, filtered[0].length).setValues(filtered);
                }
            
        

This version copies only rows where the 3rd column contains the word "Approved", including headers.

7. Conclusion

            
                Google Apps Script makes it simple to copy and manage data between sheets, 
                whether within the same spreadsheet or across documents.
                These scripts can be scheduled using time-based triggers for automated backups 
                or reporting, and can be extended with filters, formatting, or API integration.
            
        

It saves hours of manual copy-pasting, boosts reliability, and reduces user error.