Skip to content

Information for Users (of the spreadsheet)

Jump to bottom
Dan Smith edited this page May 27, 2023 · 17 revisions

This page is for people who administrate the spreadsheet, or who enter data using the non-mobile version of the spreadsheet.

You will need

Setup process

1. Request access

  1. Locate the APPS SCRIPT PROJECT EDITORS list in your organisation's internal documentation
  2. Email the app owner/developer, requesting access to the STABLE APP and providing your @gmail.com email address
  3. The app owner/developer will add you to the SPREADSHEET as an Editor
  4. The app owner/developer will add you to the associated APPS SCRIPT PROJECT as an Editor

2. Open the app for the first time

The spreadsheet uses some app functionality, so spreadsheet users need access to the app.

You can test whether you have sufficient access by opening the following menu item:

  • GCC Help > View Version History

A popup/dialog should open immediately. If no popup opens, something is wrong - i.e. you have not completed the 2 steps below.

Step 1: Give the STABLE APP access to the spreadsheet

  1. On your computer, open your usual web browser and load the STABLE APP
  2. Sign in with your Google Account
  3. Click Review Permissions, to allow APP NAME App (Unverified) to access your data on Google
  4. Choose your email account to continue to APP NAME
  5. Click Continue, as Google hasn’t verified this app - because it is still under development
  6. Click Allow for APP NAME wants to access your Google Account
  7. The web app will load
  8. Close the window/tab containing the web app
  9. App-related functionality will now work as expected

Step 2: Give the DEV APP access to the spreadsheet

  1. On your computer, open your usual web browser and load the DEV APP
  2. If you are asked to request permission, please follow the instructions to do so and the developer will grant you access
  3. Load the DEV APP again
  4. The web app will load
  5. Close the window/tab containing the web app

4. Familiarise yourself with the input types

Check-boxes

There are three types of checkbox:

  1. R (Residential Collection)
  2. B (Business Collection)
  3. X (Checklist Task)

The purpose of type X is to expose extra tasks to the collector.

For example, a collector collecting the hire bike must also collect one or two tubs of compost from an inner city drop off location. As this pickup is for the organisation rather than a customer, it is a Checklist Task rather than a Collection.

Check-boxes are added in the same way as drop-down menus:

  • GCC Help > Edit Collection Inputs > Regenerate Collection Inputs (checkboxes and dropdowns)

Drop-down menus

Drop-down menus are used in place of 'free text' fields. This ensures that the data flowing into the app does not unintentionally break it.

Drop-downs can also be more user friendly than using the keyboard, especially on a smartphone.

Container

Drop-down options are pulled from the GCC Variables sheet. If a new container type becomes available, edit this sheet to add an item to both NRContainerTypes and NRContainerCapacities.

After making edits, open Data > Named ranges and hover over each named range to check that the green outline includes all of the values in the column. If it doesn’t, click the pencil icon to edit the range.

Date (e.g. Mar 9)

Drop-down options are pulled from the GCC Variables sheet. The drop-downs are generated from a script inside the APPS SCRIPT PROJECT.

When a cell is edited in the Quantity or Container columns, the script automatically updates the non-hidden date drop-downs in that row. This process takes a few seconds and the dropdown must be closed and reopened to show the changes.

If the Container type is changed, for example if changing from 3 Buckets to 1 Tub, then existing drop-down selections will appear with a red triangle next to them. This indicates that the previously selected options do not exist in the updated drop-down menu. If a collector loads the associated date in the STABLE or DEV APP, the app will break. Other dates will continue to work as expected.

Drop-down options are set in the GCC Variables sheet. The selected text and background colours are also reflected in the run sheets, and in the mobile app.

Update drop-downs

In these scenarios:

  • After adding a new date to the run sheet
  • After updating the GCC Variables sheet in any way
  • If the wrong options appear in the drop-downs

Please run this action:

  • GCC Help > Edit Collection Inputs > Regenerate Collection Inputs (checkboxes and dropdowns)

Note:

  1. This process currently takes around 2.5 minutes.
  2. Dates in hidden columns are deliberately ignored to improve performance.
  3. Google Sheets sometimes changes the order of drop-down options. It does this to promote options which closely match the selected one. Updating the drop-down menus will not fix this.

GCC Help

This menu option appears after ~6 seconds. It is generated from a script inside the APPS SCRIPT PROJECT.

Version History

If you are unsure whether a known issue has been resolved yet, you can check the Version History:

  • GCC Help > View Version History

5. Be aware of any relevant changes to the app or spreadsheet

Ask the developer(s) if they have made any changes recently, as these could affect the way you enter data.

6. Make the developer aware of any changes to the spreadsheet

Tell the developer(s) if you have made any changes recently, as these could break the app.

Clone this wiki locally