Program Database

Introduction

We keep our key data about programs, clients, events, invoices and contracts in a google spreadsheet that serves as our database. In addition, we have important data in other tools, including:

• Quickbooks (financial transactions)

• Google Drive, Calendar, etc.

• Gusto (payroll and personnel)

• Grantseeker.io (grants, grant applications and funders)

See the "Accounts and Tools" page for a full listing.

General information about the database

• The 2nd line is sometimes used for formulas; be careful about how you use this row.

• See the README tab in the database spreadsheet for specific information about how to use the database. For example, some columns are colored to indicate that you should not enter information into those columns manually; rather the data is brought in from another location.

Adding Clients

If the client is a new client, add the client to Clients tab of database.

Adding Programs and Events

Each program that we do for a client is a project, and should be named as "2021 11 Program identifier". These are used in Gusto, Quickbooks and the database. Here is the process for adding a new project:

1. Add it to the Programs tab of the database, creating an appropriate ProgramCode in the red column. The blue Customer Type column will fill in automatically when the Client in the Programs tab matches a Client in the Clients tab.

2. [IGNORE THESE BULLETS - DO NOT CURRENTLY DO...

   1. Add it to Gusto as a project, and add personnel who are likely to log time against it.

   2. In the Programs tab of the database, note in column "In Gusto" that it has been added to Gusto. 

   3. Inform the bookkeepers that a new project has been created. (They may figure it out on their own, so this may be optional.)]

3. The exact same program name should be used in the Series (A) column of the Log tab in the database.

4. Set up the event in the Log tab of the database

   1. The program name should be used in the Series (A) column of the Log tab in the database. The blue "Match?" will show "ok" if there is an exact match to the Series name in the Programs tab.

   2. Complete the remaining information in that row of the Log tab. Do not write in the blue or grey rows; these are automatically calculated from a formula (blue) or filled in by a macro (grey). Be sure to fill in: Program, Date, Start, End, Calendar (usually "Programs"), Type ("In person" or "Other"), Location, Paid Staff Names, Volunteer Names. These fields are used to set up the event in our calendar. Be sure to give times using a 24-hour clock format.

5. Run the macro createOrUpdateCalendarEvents (from Extensions > Macros) to create the program on the calendar. For details on how this works, to help with maintaining the code, see section on Macros and Triggers below.

Adding staff

• Add their name & address in the Staff tab

Adding events: run the macro

All events and classes should be added as follows

1. Create a row in the Log tab; fill in the colum Update (with a Y), Series (should match the name of a program from the Programs tab), Program (free text), Date, Start, End, Calendar (typically Programs), Type (typically in person), Paid Staf fNames and Volunteer Names. Other white-colored columns may optionally be filled in.

2. Run the macro createOrUpdateCalendarEvents to put the event on our google calendar.

3. Check in the calendar that it was added

4. If there are any errors, report them to admin@navigationgames.org

(Note that public events that people sign up for on our website will also separately be set up in Wix as an event.)

Deleting events: run the macro

1. Add a "Y" in the yellow "Delete" column. 

2. Run the deleteLessons macro from menu Extensions > Macros. The macro should add a Y in the grey "Deleted" column.

3. Confirm that the event was removed from the calendar

4. Remove that event from the database (delete the row), or use strikethrough to indicate visually that it was deleted.

If you have an error when deleting events, it could be that one of the events you are trying to delete was already deleted, possibly manually (which you shouldn't do), from the calendar. Check if that is the case; if so, then type a Y in the blue "Deleted" column and rerun the deleteLessons macro.

Macros and Triggers

Row 2 formulas in Log tab

Do not change or remove the formulas on row 2!

• Staff: =arrayformula(regexreplace(substitute(substitute(V2:V & ", " & W2:W, "-, ", ""), ", -", ""), "^, ", ""))

  • This is using V (Paid Staff Names) and W (Volunteer Names), and removing spaces

• StaffEmailAddresses: =map(X2:X, lambda(row, if (row="", "", join(", ", arrayformula(VLOOKUP(split(row, ", ", FALSE), Staff!$C:$F, 4, FALSE))))))

  • This is using X (Staff) and looking up email addresses from the Staff tab.

  • This column is used in the macro to add people to the event.

Adding and deleting events on the calendar using the macros

Events that were added to our calendar using a macro from the database should be removed using a macro. 

From the Log tab, you add and delete events from our calendar by running macros.

Debugging Notes

3/12/25 issue that was resolved

3/12/25: there are quite a few programs that were created, and have the IDs listed, but when we go to look them up, they are not present with those IDs. Moreover, they don't have a "Y" in the Updated column; I'm not sure if that was because Mikayla was trying to update them or the code did not complete; probably the former. I'm not sure why the IDs don't map to those events. Things to do:

• Find out how to get the ID for an event from the calendar directly.

• Try changing a different existing event and see if that still works.

Update Nov 2025: not sure how this was resolved, but I think it was.

Webhook error 1/10/25

There is also a trigger that has been throwing an error for some time. 1/10/25: debugging it

• Error is in function postToSlack. The purpose of this was to get reflection links posted in Slack. I *think* that the webhook is not working.

• I went to https://api.slack.com/apps/A01UX78RXQF which has credentials to allow the app to access the Slack API. Not sure whether I'm using a verification token or the signing secret. 

• For incoming webhooks, this Slack documentation says:

  • Create a Slack app

  • Enable incoming webhooks

  • Create an incoming webhook