Teachers often face the challenge of managing classroom data in Google Sheets while ensuring students can only see the information relevant to them. You might want students to work in the same spreadsheet but not be able to see each other’s tabs. Unfortunately, in a shared spreadsheet, all tabs are visible to everyone. If you need individual tabs that can be viewed one at a time by different students, the dynamic tabs export feature in TemplateTab might be exactly what you’re looking for.
Export Tabs
If want to use Google Sheets to export tabs Alice Keeler shares how to use these new export features.
TemplateTab by AliceKeeler
Duplicate a template/graphic organizer for each student on the roster in Google Sheets™. Ability to export tabs to individual sheets.
Collects NO user data.
Dynamically Export Sheets Tabs
A feature of TemplateTab is to export the sheets (tabs) of your spreadsheet to individual sheets. This allows you to work on multiple tabs in one spreadsheets but share the individual tabs without allowing others to see all the tabs.
2 Export Options
Exporting tabs can be static or dynamic.
Static exporting of tabs makes a COPY of each sheet to a new spreadsheet per tab.
Dynamic exporting also creates a new spreadsheet per tab, but inserts an IMPORTRANGE function for each tab. This means updates you make to the tabs will show up in the individual sheets.
Extensions Menu
Exporting tabs works in any spreadsheet.
In your spreadsheet with multiple tabs, use the Extensions menu to locate “TemplateTab by Alice Keeler.” Exporting tabs is an upgraded feature with a cost of $5 PER YEAR. Premium AliceKeeler members are able to access the upgraded features with their passcode.
Choose “Export Tabs” or “Dynamic Export Tabs.”
The IMPORTRANGE Formula
While the dynamic export feature is incredibly useful, it’s important to consider security. The IMPORTRANGE function is used to pull data from your master sheet into individual sheets, but it exposes the other sheets of information.
DO NOT USE THE DYNAMIC FEATURE TO SHARE SENSITIVE INFORMATION.
When using IMPORTRANGE formula in Google Sheets™ you will need to “Allow access” for each sheet. This is a new security measure when bringing outside data or images into a spreadsheet. After allowing access, notice the =IMPORTRANGE formula in cell A1. The formula indicates the tab name.
View Only Permissions
For the IMPORTRANGE to work, make sure the main spreadsheet has viewing permissions. The imported range into the new spreadsheet can only be viewed by people who have permission to view the sheet.
Exported Sheets are Private
Exported sheets are in your Google Drive. Suggestion to organize them into a folder set to “Anyone with the link can view.” If you are sharing these spreadsheets with individual students you will need to manually share the spreadsheet with the student. Again, they can only view the “IMPORTRANGE” if they have permissions to view the original main spreadsheet.
Note: The Add-on is not authorized to manage Google Drive. This means it is not capable of organizing into a folder or setting sharing permissions.
Managing Files in Google Drive
While this Add-on is for Google Sheets and not Google Drive, it is not too many steps to clean up Google Drive manually.
Select Multiple Files in Google Drive
In Google Drive go to “Recent.” This should display the exported tabs you just created.
Single click on the first exported tab.
HOLD DOWN THE SHIFT KEY
Select the last exported tab.
All of the sheets should be selected all at once. Use the toolbar to click on the folder icon to move the files to a folder.
If the folder is set to “Anyone with the link can view” all the files inside the folder take on that property.
Reverse Dynamic Sheets
TemplateTab also offers the ability to reverse the export. Create a sheet for each name in column A after you setup TemplateTab. Edits to the SHEET (not the whole workbook) on the exported sheet will be visible back in the main spreadsheet. This is done via IMPORTRANGE but with the formula on the main spreadsheet instead of the exported one.
