Pulling data from most recent google sheet

Question

I am currently working on a sales sheet for my team and have a question.

I currently have each month separated into different sheets for ease of use with our current workflow and would like to be able to pull averages for each employee but would prefer it only be averages for the most recent month. Is there a way I can read either the most recently created sheet or with the latest month name and feed that sheet name into a formula?

A few of the ideas I had were possibly using a script found here to create an array listing all the sheets and using an INDEX-MATCH in order to find the latest month and input in into a formula but I'm having issues getting the script to update when a new sheet is made.

Another idea I had was just to use a FILTER or a QUERY to aggregate every transaction into one table on another sheet and pull my averages from that sheet instead but I'd prefer not to use a calculation table if I can avoid as it will get pretty messy with all the employees added. Any help of suggestions would be much appreciated.

Answer 1

From the question

Is there a way I can read either the most recently created sheet or with the latest month name and feed that sheet name into a formula?

The easier way to do this is by using Google Apps Script, perhaps by using a custom function.

You could use developers meta-data to save the date that each sheet was created but it will be a way more easy if you use the sheet name rather than this alternative.

Having a list of the sheets is an an option but you should somehow update that list. To do this automatically you could use a on change installable trigger.

References

• https://developers.google.com/apps-script/guides/sheets
• https://developers.google.com/apps-script/guides/sheets/functions
• https://developers.google.com/apps-script/guides/triggers/installable