How to get the count of the current sheet?

Question

I have a spreadsheet that I have spread out over many sheets. However, I could automate the filling out of some simple data by doing some simple calculations. I could program something simple like this, but I have been looking at Google's function list, and I cannot see anything that would allow me to do this. Everything I need to automate needs access to the current sheet number.

So these are the properties of my spreadsheet...

• I have 7 different classes I am looping through, and each one of them have specific things that need to be done for the months of December, January, and February

• Each month is a new spreadsheet (Should be automated - 1 cell)

  • Sheet 1 - December ---- NOTE: Sheet 1, 2, and 3 are for 1 Class (ie. DA - December)
  • Sheet 2 - January
  • Sheet 3 - February
  • Sheet 4 - December
  • Sheet 5 - January
  • etc..

• For each month, things are divided into 2 weeks. (Should be automated - 2 cells)

  • Sheet 1 - Week 1, Week 2
  • Sheet 2 - Week 3, Week 4
  • Sheet 3 - Week 5, Week 6
  • etc...

• For each month, there are only 6 items (3 items per week) (Should be automated - 6 cells)

  • Sheet 1 - Day 1, Day 2, Day 3, Day 4, Day 5, Day 6
  • Sheet 2 - Day 7, Day 8, Day 9, Day 10, Day 11, Day 12
  • etc...

I have been trying to find functions that could allow me to do this, but I don't even know if it is possible. I have seen that there use to be a script gallery, but that is not the add-on manager. I have not seen anything in there to solve my issue

---

I figured if I could get the current sheet count, then I could easily do this myself. Just apply a little algebra and some though and I could automate this process.

For the months, I can have a formula with modulus - 1, 2, 3, 4, 5, 6, 7, etc, and turn it into ... - 1, 2, 3, 1, 2, 3, 1, etc..

For the weeks, I can have a simple formula like

• = ([current sheet #] * 2)
• = ([current sheet #] * 2) - 1

For the days, I can have a simple formula like

• = ([current sheet #] * 6)
• = ([current sheet #] * 6) - 1
• ...
• = ([current sheet #] * 6) - 6

Answer 1

It sounds to me like you have a fundamental design flaw if each month is a new sheet, and there is nothing on the sheet to show the current month.

I would suggest putting all the data onto one sheet, and then adding per-month extracts onto other sheets using the various summarisation and extraction tools (pivot tables etc).

Answer 2

At this time there isn't a built-in function that gives the number of sheets, so you have to think around the alternatives to Extend Google Docs, Sheets, and Forms with Apps Script.

It's worth to say that sheets doesn't have an inherent number, so you should determine how do you will assign a number to each sheet. One way is to assign a numerical prefix as part of the sheet name.