Best answer

Create a Google Sheet with a worksheet per Calendar even in a week

  • 20 January 2022
  • 5 replies
  • 107 views

So I am trying to setup something for our community in order to track any potential Covid exposures.

Each week we have an unknown number of events (1+N), I am currently able to create a spreadsheet from a template for each event using the X number of Days before event start in google calendar.

What I would like to do instead, is create a single spreadsheet with 1+N workbooks, named for the event in question, per week. 

As I understand it right now, I dont see a way to:

  1. name the workbooks
  2. pull ALL events in a given week (I can pull the first one that matches a search criteria, but then the system stops there)

Open to any suggestions but more or less the only tools I have access to is GSheets and GCal for this :(

icon

Best answer by SamB 27 January 2022, 12:36

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Userlevel 7
Badge +11

Hey @Boris K, welcome to the Community! :)

Google Sheets has a Create Worksheet action which you could use to add additional worksheets to a single spreadsheet. You could then take the name of the event from the Google Calendar trigger to use as the name of the workbook. For example: 

13cec54d4196d2f8dd27d3e1d2d62e1e.png

What I’m a bit unclear on is what you mean by “pull ALL events in a given week”. Is it that you’re looking to create separate worksheets for each week and add the details for each event (from that week) into the rows on that worksheet? 

Ok this is the overall effect I am trying to achieve:

 

I am trying to recursively go through a week (Mon-sun), for that specific week:

  1. create a spreadsheet (new file) from a Template file
  2. in that new file create a worksheet for each event I find
  3. Copy a template worksheet and rename the new one something specific
  4. Update a couple of rows
  5. Repeat until I run out of events for that week
  6. Delete the template worksheet
  7. Repeat the process next week

End result should be a File with X number of worksheets, all named something different.

 

Of all the sheet actions Create Worksheet is the only one that lets me name a sheet, however, I already have a Template Sheet that is setup the way I need it to be. Copy Worksheet can copy it, but does not let me name it, which ends up with me having a Sheet full of Copy of TEMPLATE and Copy of Template (1), etc etc etc

Userlevel 7
Badge +11

Thanks for clarifying that, @Boris K!

I did some digging and there’s an existing feature request open for the ability to set the name of the new worksheet when using the Copy Worksheet action. So I went ahead and added your vote for that. I can’t give any promises as to when that functionality will be added to the Copy Worksheet action but we’ll definitely reach out to you by email as soon as it is!

That aside, I’m not sure Google Sheet is going to be the best place to handle this workflow. I’m wondering if it might be better to use an app like Airtable instead. It’s similar to Google Sheets but has more database-like features. So you’d be able to add all the information into a single place but could set it to group the different rows (records) of data or set up different views based on the dates. They’ve got a free plan so may be worth a look to see if it will do what you’re after? 

Unfortunately Airtable is not something I can use due to restrictions within which I have to operate the workflows :(