Merge multiple sheets into a new summary spreadsheet

About

Summary: Concatenate data from multiple sheets into one new Google spreadsheet. The initial data is not deleted.

Applications: Google Drive, Google Sheets

Description:

This examples explains how to create a workflow that gathers data from several Google spreadsheets and merges them into a new sheet, while preserving the initial data.

This example presents Google Workspace activities such as Add Sheet, For Each Sheet in Spreadsheet, Read Range, and Write Range.

Workflow

Tip: If you want to save time, use the Merge Multiple Sheets into a New Summary Spreadsheet template in Studio Web to build this workflow.

1. Open Studio Web and create a new project.
2. Add an Add Sheet activity to your workflow. At this step, you want to add a new sheet, called Summary, to a specific spreadsheet. In the activity:
   1. Set up your Google Sheets connection.
   2. In the Spreadsheet field, browse and choose the spreadsheet where you wish to create a new sheet.
   3. Set the Sheet name to Summary, for example. Choose any name that suits your usecase best.
   
   

   

   
   
3. Next, add a For Each Sheet in Spreadsheet activity, to iterate on the sheets you wish to merge. In the activity:
   1. Select the same spreadsheet.
   2. In the Body of the activity, add an If activity. Configure the activity as follows:
      1. Set the Condition for the iteration. Click the Plus icon to select Use variable.
      2. Set the condition as: CurrentItemIndex=1.
      
      

      

      
      
4. Next, add an activity for the Then branch of If. This is the activity to execute in case the condition is true. Add a Read Range activity. Its purpose is to use the first sheet headers in the Summary range and copy its data. Configure it as follows:
   1. Select the spreadsheet.
   2. For the Range field, click the Plus icon to select Use variable, then select CurrentItem.
   3. Set the Has headers field to True.
   4. Set the What to read field to Values.
   
   

   

   
   
5. Right after the Read Range activity (still under the Then branch), add a Write Range activity and configure it as follows:
   1. Select the same spreadsheet.
   2. For the Range field, click the Plus icon to select Use variable, then select the output variable of the Add sheet activity.
   3. Set the Include headers field to True.
   4. Set the How to write field to Overwrite.
   5. For the What to write field, select the output variable of the Read Range activity.
      
      

      

      
      
6. Add a Log Message activity to record that the first sheet (CurrentItem) has been added to the Summary sheet (New sheet name). This step is optional.
   1. Configure the Message field as follows:
   
   

   

   
   
7. Next, add another If activity and set a condition that determines if the next sheets to be read are different than the Summary sheet. At this step, we copy the other sheets, excluding the headers.
   1. Configure the Condition as follows: CurrentItemIndex variable greater than 1.
      
      

      

      
      
8. Set the Then branch. Add a Read Range activity to read through the sheets in the spreadsheet:
   1. Select the spreadsheet.
   2. For the Range field, select Use variable and choose the CurrentItem variable.
   3. Set the Has headers field to True.
   4. Set the What to read field to Values.
      
      

      

      
      
9. Next, add a Write Range activity, to write the data read into the Summary sheet.
   1. Select the spreadsheet.
   2. For the Range field, select the New sheet name variable of the initial Add Sheet activity.
   3. Set the Include Headers field to False.
   4. Set the How to write field to Append. To learn more about appending, see the activity's documentation.
   5. For the What to write field, select the output variable of the Read Range activity.
      
      

      

      
      
10. Next, add a Log Message activity, still under the Then branch, to record that all the sheets have been appended into the Summary sheet. This step is optional.
    
    

    

    
    
11. You're done and ready to run your project!