Google Sheets Duplication and Migration Automation - n8n Workflow

• Data analysts who frequently need to create backups or copies of complex spreadsheets with multiple tabs.


• Automation engineers looking for an advanced n8n template for Google Sheets API operations.


• Teams needing a consistent, automated method for sheet migration or archival.

Managing Google Sheets with numerous tabs can be time-consuming, especially when migrating or archiving data. Manually copying sheets and ensuring data integrity is tedious and prone to human error.

This specialized n8n workflow solves this problem by providing a reliable, automated solution. It uses the Google Sheets n8n node, combined with advanced API calls and looping logic, to ensure every sheet (tab) from your master spreadsheet is created and populated correctly in a brand new destination file. This powerful n8n template demonstrates how to orchestrate complex operations using core logic and service-specific nodes.

This automation initiates via a manual n8n trigger and follows a meticulous process:

1. Initiation and Creation: The manual n8n trigger starts the process. The first step uses the Google Sheets n8n node to create a brand new destination spreadsheet, capturing its ID for subsequent operations.


2. Discovery: An HTTP Request n8n node connects directly to the Google Sheets API to fetch the metadata of the Master Spreadsheet. This response contains the names and properties of all sheets (tabs).


3. Data Preparation: A Code n8n node processes the API output, extracting the sheet names and formatting them into individual items suitable for batch processing.


4. Looping and Iteration: The workflow uses the Split In Batches n8n node (titled 'Loop Over Items') to iterate over each extracted sheet name one by one, preventing API overload and maintaining sequential order.


5. Creation and Copy: Inside the loop, two sequential Google Sheets n8n nodes perform the core duplication: first, a new sheet is created in the destination file using the dynamic sheet name. Second, data is read from the corresponding sheet in the master file and written (appended) to the newly created sheet.


6. Cleanup: Once the 'Loop Over Items' n8n node finishes processing all sheets, it fires its 'done' output. A final Google Sheets n8n node is executed to remove the default 'Sheet1' automatically created by Google, leaving only the duplicated sheets.

To deploy this advanced n8n workflow, follow these steps:

1. Import: Copy the provided JSON code and import it directly into your n8n instance as a new workflow.


2. Credentials Setup: You must configure and connect your Google Sheets OAuth2 API credentials. This requires enabling the Google Sheets API in the Google Cloud Console.


3. Master ID Configuration: Locate the 'HTTP Request' n8n node and the 'Read Spreadsheet1' Google Sheets n8n node. In both places, replace the placeholder YOURMASTERSPREADSHEET_ID with the actual ID of the source sheet you wish to duplicate.


4. Testing: Click the manual n8n trigger to test the workflow. Ensure all credentials are set up correctly before activating the n8n workflow for production use.

This n8n workflow relies on the accurate configuration of several key nodes:

When clicking 'Test workflow' (Manual Trigger n8n trigger):
Function: Serves as the initiation point for running this n8n workflow.
Key Configuration: Starts the automation instantly upon manual execution.

Create New Spreadsheet (Google Sheets n8n node):
Function: Creates the destination document where all copied sheets will reside.
Key Configuration: Operation set to create and Resource set to spreadsheet. The title is customizable (default: 'Copy of Master Spreadsheet').

HTTP Request (n8n node):
Function: Interacts with the Google Sheets API v4 to retrieve metadata, specifically listing all sheets within the master spreadsheet.
Key Configuration: URL uses expression to include YOURMASTERSPREADSHEET_ID.

Code (n8n node):
Function: Parses the complex JSON array received from the HTTP request, extracting the essential sheetName for use in the subsequent loop.
Key Configuration: Uses JavaScript to map the API response data into simplified n8n items.

Loop Over Items (Split In Batches n8n node):
Function: Ensures each sheet is processed sequentially, one batch (item) at a time.
Key Configuration: Splits items from the Code n8n node into batches of
1.

Create Sheets (Google Sheets n8n node):
Function: Dynamically creates the new sheet in the destination spreadsheet using the sheet name passed through the loop (e.g., {{ $('Loop Over Items').item.json.sheetName }}).

Read Spreadsheet1 & Write sheet (Google Sheets n8n node):
* Function: These two n8n nodes work in tandem. The first reads the entire dataset from the master sheet, and the second writes (appends) that data to the newly created sheet in the destination document, completing the duplication for that specific tab.