How do I automatically create tabs with specific custom information?

[Fiona Bellinger]

[Moderator insert: this is an advanced topic that is not for people new to G Suite or Google Sheets. It delves into scripting and programming languages so is not for everyone ;-)]

Hi there,

This one is a little hard to explain so check out the attached spreadsheet…

I have two sheets on a spreadsheet – Basic_info and Data_Sheet.  These ones are very trimmed as they normally have about 100 names on the Basic_Info sheet, and 500+ lines of data imported from another sheet on the Data_Sheet.

I need to produce a tab for each name, which combines information from Basic_Info and Data_Sheet.  Latest Date sorted, and automatically updating as data entered.  I’ve got all this part sorted, but was wondering if there was a script to quickly produce the required tabs.  Duplicate and change name in cell C3 and Tab Name is quick, but when I’ve got 800 students in total I’m also getting a sore wrist doing this!!!

Check out the Tabs labeled A, E M, O, T to show what I want.  Just imagine another 100 tabs on this spreadsheet.

Any suggestions?

ASSIST Spreadsheet

Thanks

Fiona

• This topic was modified 6 years, 7 months ago by Rob Clarke.
• This topic was modified 6 years, 7 months ago by Rob Clarke. Reason: inserted moderator comment at start of thread to let members know this is quite an advanced topic

[Rob Clarke]

Hi Fiona,

Can you give me edit access to the Sheet please? This sounds like it is definitely a script job so may take a bit of research to solve!

Rob

[Fiona Bellinger]

Hi Rob,

https://docs.google.com/spreadsheets/d/1twxjJSNGpNtGNU_EZA1qqMzQ14kqKRDV1P3VZ0M_M-c/edit?usp=sharing

Changed it to edit access for anyone with the link, so hopefully that helps.

I thought it would be a script, which is something I have absolutely no experience with.

 

Fiona

[Rob Clarke]

Hi Fiona,

Still researching this for you… 😉

Here is a video about scripting using Google Apps Script:

While you wait, you might like to have an explore through this getting started area for scripting for Sheets. Some of the other links I came across that might help include:

• Macros in Sheets…
• Custom menus…

If you find items of particular use before I do, let me know back here!

Rob

• This reply was modified 6 years, 7 months ago by Rob Clarke.
• This reply was modified 6 years, 7 months ago by Rob Clarke.

[Rob Clarke]

Hi Fiona,

My suspicion was right, you will have to do this by learning to write using Google Apps Script – which is getting very technical, very quickly. Basically, you’d have to learn some basic javascript.

The response to my question for you was to check out the same links I’ve shared with you, plus there is a forum in Stack Overflow (a community for developers and programmers) dedicated to this where you could get programmers from around the world help you if you want to give it a go.

References:

• Google Apps Script
• Extending Google Sheets
• Quickstart: Macros, Menus, and Custom Functions
• Stack Overflow

Do you want to try this together? I’m not sure if this will be the fastest way for you to get the outcome you want though…

Rob

[Fiona Bellinger]

Thanks – I had posted it in Google Apps Script too so will recheck if got any answers that way.  Will also post in Stack Overflow as suggested.  My wrist is still injured so any script would be wonders, but will work on it slowly.  Meanwhile I have another question 🙂 . Which I’ll post separately…

 

[Rob Clarke]

Cool, if you have a dodgy wrist I hope it isn’t from overuse of the computer!

You should see a physio or someone if so, as it doesn’t get better from using the computer 😉

Rob

• This reply was modified 6 years, 7 months ago by Rob Clarke. Reason: Needed additional tags to specify advanced topic

[Fiona Bellinger]

It is from computer use, so trying to take it very easy.  Having it checked also, and asking work to bring in OT 🙂

 

[Rob Clarke]

Hey Fiona,

I just heard back from the Google Docs Support Specialist and there is another place which might be better for getting help on this – the Google Apps Script G+ community.

I think this would be the best place to get help on this more advanced topic. I’ll join it and then if you post to that [email protected] to tag me in 😉

Rob

[Rob Clarke]

Hi again @fbellinger16vanasch-school-nz,

I just got two responses to my query in the G+ community – they look promising:

function copyTab() {
var ss, sourceSheet, sourceData, sourceDataRange, newSheetTab;

ss = SpreadsheetApp.getActiveSpreadsheet();//Get active spreadsheet

sourceSheet = ss.getSheetByName(“Sheet1”);//Get the source sheet tab
newSheetTab = ss.insertSheet(“New Sheet”);//Create a new sheet tab

sourceDataRange = sourceSheet.getDataRange();

sourceDataRange.copyTo(newSheetTab.getRange(1, 1));//Copies the data from a range of
//cells to another range of cells. By default both the values and formatting are copied

}

…and another to check Alice Keeler’s RosterTab2 – plus, here is the step by step tutorial for this particular script which I think is what you are after 😉

Let me know how you go – plus I hope the arm/hand is okay.

Rob

• This reply was modified 6 years, 7 months ago by Rob Clarke.

[Emma Riddell]

Hi there,

How are you getting on with this? I tried something similar with staff leave, and had individual tabs for each staff member etc, but instead of using arrays and vlookups I used query reports. However, after also adding a couple of charts it all stopped working – I had reached the data limit of google sheets, and we only have 30 staff!

I suspect you may run into this faster if you are going to have 800 tabs, one each for all students? My work around was using just one query where I change the parameters each time I want to report on a particular staff member. Much cleaner and far less scrolling…

[Fiona Bellinger]

Thanks for this both – my task tomorrow to look at this…. Your help and direction is really appreciated 🙂

[Fiona Bellinger]

Do you hear my scream of satisfaction when I got it to work 🙂 🙂 🙂

Thanks Rob, I have used one of the tutorials by Alice Keeler which uses a template as well as a tab creater.  And that combined with a little script referencing the Tab name as a cell from which data is selected means it is all automatic now.  No stuffing my wrist 🙂

I’ve cleaned and shared my spreadsheet if you want to see it at work.  Just run the Template Tab at the top.

https://docs.google.com/spreadsheets/d/11E0nS37SpmcBM0eSmRn6Je55QOjQOWyu6bn6z9OAYHQ/edit?usp=sharing

The final (i hope) thing is that I want to protect all but one of the spreadsheets.  Not a biggy but if I can have read only for majority of the tabs it would save potential corruption and means that the people who have access to it can only enter data in one place.  Prob being is that I really can’t be bothered going in and individually locking all tabs, and it wont do it automatically based on the Template Tab being locked.

Again, thanks Rob and Emma for your help and encouragement.

Fiona

 

 

[Rob Clarke]

Hi Fiona,

Whoop, whoop! You are so clever! I can’t see it run as the tab ‘template’ is protected ;-(

The final (i hope) thing is that I want to protect all but one of the spreadsheets.  Not a biggy but if I can have read only for majority of the tabs it would save potential corruption and means that the people who have access to it can only enter data in one place.  Prob being is that I really can’t be bothered going in and individually locking all tabs, and it wont do it automatically based on the Template Tab being locked.

There are two Add-ons in the Resource Library which might do this:

• editLock which is a new Add-on which may achieve this for you.
• Bulk Sheet Manager

…what I don’t know is whether you can automate the locking process, but editLock looks promising for this – I suspect it is intended to automatically lock based on a form submission.

Here is the search I did in the Resource Library:

[Author]

Posts