Google Sheets
Google Sheets + Google Sheets Integration
Distribute data from one Google Sheets into many Google Sheets, take backup and more
What is Sheet Director?
Sheet Director is a Google Workspace add-on that helps to manage and control bi-directional data flow between Google Sheets and several other applications like Google Calendar, Tasks, Contacts, Sheets, QuickBooks, FreshBooks, Zoho etc.
It is available in the Google Workspace Marketplace. Please check the Installation instructions to install the add-on.
What is Google Sheets?
Google Sheets is a spreadsheet program included as part of a free, web-based software office suite offered by Google within its Google Drive service.
In Sheet Director Google Sheets application is used to segment a field by using a particular parameter and store the particular data in a new Google Sheet or an existing Sheet.
Use Case(s)
> Distribute: Segment data from a source sheet and send that data across 'n' number of sheets based on configuration.
> Backup: Backup the whole sheet to the drive folder.
> Consolidate: Combine data from multiple spreadsheets into a single spreadsheet.
Google Sheets
Sheet Director
Google Sheets
Distribute Service
Choosing App and Service
Select Google Sheets from the list of applications
Select service as Distribute or Backup
Distribute sends sheet data to different sheets.
Backup sends a backup to the selected folder.
Select the Google Sheet configuration required.
Select the distribution strategy required
Segment data
Filter data
Segmentation is a process of grouping the spreadsheet data by values of the selected field.
Filter is a process of grouping spreadsheet data based on filter conditions.
Filter data
Select Full Sheet or Range.
If Range is selected mention the range of values that is required.
Mention the required filter name.
Select the required matching conditions. The matching conditions are all & any.
Mention the required filter values.
Choose the required field name.
Choose the operator.
Mention the required value.
Click on the "+" icon to add more conditions.
If the filters match the data, then the data will be distributed into the corresponding spreadsheet.
Setup the spreadsheet to which the filtered data is to be distributed.
Click on Choose to select an existing spreadsheet.
Click on Create to create a new spreadsheet.
Select the required spreadsheet tab to store data.
Click on Add Filter to add more filters.
If none of the filters match the data, then the data should be distributed to another spreadsheet.
Switch on Otherwise to setup the Otherwise Sheet.
Click on Choose to select an existing spreadsheet.
Click on Create to create a new spreadsheet.
Select the required spreadsheet tab to store data.
Original Sheet
Filter output sheet
Here the branch rating greater than 2 is displayed as per the given condition.
Segment data
Google Sheet Configuration:
Select Segmented data.
Select the sheet that needs to be configured.
Select Full Sheet or Range.
If Range is selected mention the range of values that is required.
Segmentation:
Segmentation is a process of grouping the spreadsheet data by values of the selected field.
Segment by the field as required.
Choose the spreadsheet and spreadsheet tabs in which the data needs to be saved.
or
Create a new spreadsheet to save the required data.
Click the save icon to save the details or cancel icon to cancel.
If the spreadsheet is configured it will be highlighted with blue color.
Switch on Auto create new segments if required.
The purpose of Auto Create new segments is to automatically create new spreadsheet with segment name if the segments are not configured.
Switch on Clear old sheet data if the the data from the selected sheet needs to be cleared before saving the current details.
Click on Select Folder to select a folder so that the generated sheet will be saved in the selected folder.
Google sheet Name template appears after switching on Auto create new segments. Mention the template name as required.
Scheduler Settings:
Switch on Run automatically to execute services automatically in background.
Notification Settings:
Configure the Notification settings to setup the email templates and add attachments.
Click on execute to pull the data into sheet.
Click save to save the service.
Original Sheet
Source sheet from which you need to distribute data across different sheets.
Output Sheet
Output sheet where data is distributed to one of the configured output sheet. (From above source sheet all Queries assigned to "Mithun" will be distributed to configured output sheet)
Backup Sheet Service
Demo Video: How to integrate Google Sheets & Backup Service?
Backup sends an entire spreadsheet to your Google Drive.
Select service as Backup
Click on execute to backup the sheet
Drive Backup
Backup Tab Service
Demo Video: How to integrate Google Sheets & Backup Tab Service?
BackTab service that creates a backup of the selected sheet tab into a new one.
Select service as Backup Tab.
Select the Sheet
Choose the Tab Name from the drop-down by clicking on 3 dots.
Click on Execute Button
Sample Output
New sheet tab is created using the Backup tab service.
Consolidate service
Consolidate service combines data from all selected spreadsheets into one destination sheet.
Select the application as Google Sheets.
Select the service as Consolidate.
Choose the spreadsheet that contains data.
Select the conditions as all or any.
Choose the field name for filter.
Choose the required operator.
Mention the required filter value.
Click on Add Filter to add more filters.
Click on Add Spread Sheet to add more spreadsheets.
Consider this scenario: Class X of a school has two sections X.A & X.B. Both section students have attended an exam and their marks are generated separately. Now the teacher needs to get the list of candidates from both classes who have failed the exam in a spreadsheet
Marks of students X.A
Marks of students X.B
From Marks of students of X.B
Select the required filters.
Here 2 filters are used.
Filter by Remarks
Filter by marks
From Marks of students of X.A
Select the required filters.
Here 2 filters are used.
Filter by Remarks
Filter by marks
Select the required destination spreadsheet.
Click on execute to pull the data into sheet.
Click save to save the service.
Sample Output
Here only the students failed with less than 200 marks is displayed here.
Automate Service
Demo Video: How to integrate Google Sheets & Automate Service?
Choose Automate as the Service
Automate Service has Three major sections
Trigger
Conditions
Actions
Trigger
A trigger is an event which is captured and identified
For Example: A change in the Google Sheets tab, or a Change in a Particular Row.
Available Triggers are:
Sheet is Updated: Trigger is Identified when there is a change in the Google Sheet tab.
Column is Updated: Trigger is Identified when there is a change in the selected column of the Google Sheets.
Range is Updated: Trigger is Identified when there is a change in the selected range on Google Sheets.
Row is Updated: Trigger is Identified when there is a change in the the selected row on Google Sheets
Conditions
A circumstances or requirements that need to be met for executing the actions,
(This is optional, if you do not want the conditions then you can simply turn off the On Condition Switch)
Available conditions:
Condition on Column: Conditions which can be applied to the Columns of Google Sheets
Condition on Range: Conditions which can be applied to the Ranges of Google Sheets.
Actions
Actions are the operation which is going to be performed after on the Trigger is executed or the conditions are satisfied
Available Actions:
Copy Row
Sort Sheet
Delete Row
Hide Row
Share Spreadsheet
Add Note
Remove Duplicates
Use Cases:
With this you can resolve a lot of real-life use cases here are a few.
Removing the duplicate records whenever a new row is updated in the Google Sheets.
Automatically Sort a Google Sheet by a column when a new row is updated.
Automatically copy updated row values to the new sheet tab.
Share the Google Sheets with the user email on certain conditions
Send emails to the recipients whenever a new record is updated in the Sheets.
Sample
Let's Assume one of our sample from the above use case
"Removing the duplicate records whenever a new row is updated in the Google Sheets".
Choose Sheet Updated as the Trigger and select the Google Sheet which you're looking for.
Choose Remove Duplicates as an action.
(The conditions are not required, so skipping)
FAQs
What is the maximum number of fields Segmented in Google Sheets ?
Currently 'n' number of fields can be segmented. There is no limit for the number of fields to be Segmented.
How to find the configured and non configured data in segments section of Google Sheets ?
The data which is configured will be highlighted with blue colour , and the data which is not configured will not be highlighted.
Is segmenting by Date field is possible in segmentation ?
No, Segmenting by Date field is not possible in segmentation . Date format is not supported in the segments section