Working with the the Google Sheets

Owned by Sal Hamerman
Last updated: Mar 30, 2022 by Peter Green

Jump to section:

Frequency

  • A single script generates two spreadsheets, NAFProduction and OnlineSave, each weekday evening (M-F) at 10pm. To verify this, go to File > Version history > See version history

Adding New Staff

  • To add new staff to the reporting system, add their netID to the current year's SACO sheet (e.g. '2022 SACO statistics')

NAFProduction

  • There is one tab (one sheet) per month
  • The data is overwritten by data in the NAFProduction txt files, so this Google Sheet should be considered read-only (because any changes will be overwritten).
  • If you truly need to look around within a tab, be mindful that the top row (with column names) stays frozen.
  • NOTE: this spreadsheet has hidden tabs for previous year’s months. It may become necessary to save an archived copy of the spreadsheet and then delete the old (hidden) tabs from the working sheet.

Archiving previous year’s NAFProduction data

Simply hide the previous year's tabs. The first tab should be the January tab of the new year. Example: 202301

Note: you do not need to create an additional spreadsheet to archive NAFProduction data at the start of the year. Simply hide the previous year's tabs. 

OnlineSave

The OnlineSave spreadsheet is the primary working document for the CAMS Authorities Committee. It includes all the headings for the calendar year, pulled from the macro’s OnlineSave .txt files. 

Each year, the script should generate a new tab for the current calendar year. Alternatively, you can create a tab before the start of the new year and label it: for example, 2021 (no spaces, punctuation, etc.).

Archiving previous year’s OnlineSave Sheet

Copy the entire existing year’s tab into a new sheet, i.e. ‘2020 OnlineSave.’ This sheet should be saved to the stats folder in the Authorities Committee team drive. Once archived, the tab can be deleted from the current OnlineSave sheet.

Setting up the OnlineSave sheet

 Hiding Column A

  • In the OnlineSave sheet, column A is hidden from view, so catalogers don’t confuse the numbers in this column with the save file numbers in column B.
  • To do this, highlight column A and select the Hide Column option from the column’s drop-down menu.


Filters vs. Filter Views

  • Regular filters hide data for everyone (including the scripts) which can result in duplicates and other messes. Please do not use filters!
  • Filter views do not alter the data, and can be created for each committee member -- use them!
  • As of Oct 2019 there’s a script that removes any regular filters each evening -- just in case -- but still, best not to apply them
  • To create a new filter view, highlight the reviewer column (Column G) and then click the ‘Filter’ tool in the top toolbar and select Create New Filter View.

  • Click the green inverted triangle icon that appears at the top of Column G to set the parameters of the filter view. You will see a menu pop up that looks like this:

  • Under ‘Filter by values’ uncheck everything except the name of the committee member you’re creating a filter view for. In the above example, I’m creating a filter view for headings marked for review by the whole committee. Scroll down and press OK.
  • Give your filter view a name (in the gray Filter View header that pops up):

  • You can view and/or apply saved Filter Views by clicking the Filter icon in the top toolbar:
  • If your filter view is not working for recently added rows, you will need to Update Range (i.e. G1:G8000) in the gray Filter View header at the top. Make sure the second value in the range goes all the way to the last numbered row in the sheet. 

Version History

  • You can see and even restore previous versions of Google Docs by going to File > Version history. Use with care to avoid losing work.


Validation

    • We use the Data Validation feature to create the drop-down menus in the reviewer and is_done columns. This feature is very useful to make sure that entries stay consistent.
    • The validation pulls menu selections from the columns in the separate ‘validation’ tab:

    • To set up validation for column G, highlight the column and then select Data > Data validation in the top menu:

    • In the menu that pops up, enter the following:
      • Cell range - '2021'!G1:G1000
      • Criteria - ‘List from a range,’  =validation!$A$1:$A$12



    • Keep the default options in all other fields.
    • To set up validation for column H, highlight the column and then select Data > Data validation in the top menu.
    • In the menu that pops up, enter the following:
      • Cell range - '2021'!H1:H1000
      • Criteria - ‘List from a range,’  =validation!$B$1:$B$4
      • Keep the default options in all other fields.



  • NOTE: validation can slow the sheet down significantly as it grows.

Adding Colors

  • You can use conditional formatting to make the sheet more readable with color coding.
  • Conditional formatting is used on the reviewer and is_done columns. 
  • To assign a color to each reviewer, highlight the reviewer column (Column G) and select Conditional formatting from the column’s drop-down menu. You can also add conditional formatting by selecting Format > Conditional formatting.
  • You will see a conditional formatting menu pop up on the right side of the screen.

  • Select “Add Another Rule.” You will see the following menu pop up:
  • Under “Format rules,” select “Text is exactly” and type the reviewer’s name in the text box below.
  • Under “Formatting style,” click the paint bucket to assign a color to that person. 

  • Make sure the colors you choose are easy to distinguish from one another.
  • You will also add conditional formatting for the option “DONE” on Column H by selecting that column and following the same steps.
  • NOTE: conditional formatting will slow the sheet down significantly when as it grows. 

Slowness

  • It will slow down as it gets larger, toward the end of a year (especially if there are conditional formatting rules and validation - see above).


Troubleshooting OnlineSave

If OnlineSave seems to be broken ...

  • Don't panic. Work is not lost. 
  • Check to be sure that the first tab in the sheet (including hidden sheets) is the current year.
  • Look at the sheet's version history for clues: File > Version history > See version history
  • Make sure that there are no filters applied to the entire sheet ('Filter Views' are ok)