-1

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
  • Why are you using several sheets instead of a single one? – Rubén Jun 4 '15 at 2:41
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).

  • OK, let me explain more. I have 7 classes. Each class has stuff for Month 12, 1, and 2. So There are sheets Class 1 - 12, Class 1 - 1, Class 1 - 2, Class 2 - 12, Class 2 - 1, etc.... This works for me, so I don't see this as a design flaw. Just to clear it up. – Christopher Rucinski Dec 4 '14 at 10:57
  • So this is how the sheets will look like. December, January, February, December, January, February, December, January, February, etc... The Sheets will be named by "Class - Month". If I can get the current sheet number, then I can do a modulus plus some logic, and display the correct information in the sheet name and in a cell within the sheet. So, yes, there will be cell with the current month on every sheet – Christopher Rucinski Dec 4 '14 at 11:00
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.