User-friendly solution for scheduling and receiving notifications for daily events. Just plug in scheduled events using Google Sheets on the web. Phone calls, text messages and emails with the messages of your choice are scheduled and sent to the number / email address that you specify.
The dotnet-sheets-notifications solution orchestrates the integration of services to support the sending of automated messages, as illustrated in the figure below.
The figure bellow represents the notional architecture of dotnet-sheets-notifications. The image is docker-based and therefore highly portable. The solution takes a number of dependencies on external services. These services are configured through configuration files (secrets) that are stored seperate from the container and bound at runtime.
Specific statistics and outputs of interest would be:
- Azure DevOps Build and Deploy Pipeline and Status
- Docker Hub image repository for the Solution
- Public HTTP Health Check for the Solution
The program requires specific configurations to work for your accounts and situation. The highlights of these configurations are covered below.
- Google Sheets
- Spreadsheet Format - The spreadsheet must apply the exact headers shown in the image above: Date, Time, Type, Destination, Message, and Processed.
- Processing - Messages are processed as soon as the date / time passes for rows where the processed field is set to "FALSE". The processed field is updated by the program when the message is processed.
- Message Types - The 3 types of message are: PHONE, SMS and EMAIL.
- Spreadsheet Tab Name - Set to "TiggerList" using the static readonly variable 'Google.Sheet' in appsettings.json.
- Spreadsheet ID - Set as a property in appsettings.json. The Google Sheets spreadsheet ID can be found in the Sheets URL.
- Permissions - Permissions need to be granted to a service account to update the spreadsheet. This access then needs to be exported as a client_secrets.json file from Google Sheets and imported into the project.
- Azure
- Google Secrets - The Google client_secrets.json and appsettings files should not be shared or made publicly accessible. These can be stored as static secrets (I use Azure Blob storage) and mounted via mappings in the docker-compose.yml.
- Config Values - Configuration values stored in appsettings.json and are available through dynamic configuration in the program.
- Code
- Spreadsheet Tab Name - As mentioned earlier, the spreadsheet tab name can be set using the static readonly variable 'sheet' in GoogleSheetTrigger.cs.
- Timing - The timing is set staticly in a Quartz.SheetsNotificationJob CronTab value.
- Time Zones - Processing times are currently set in EST (not currently configurable). Times are baselined to UTC and can be set to your timezone with the static variable estZone in GoogleSheetTrigger.cs. Timezone settings are platform agnostic -- either Windows (e.g. "Eastern Standard Time") or IANA settings (e.g. "America/New York") can be used.
- Subscriptions
- Accounts - Active Azure, Twilio and SendGrid subscriptions and credentials are required. Trial accounts will work for this purpose.
- Credentials - Subscription credentials are stored in the appsettings.json file (locally) or Application Settings in Azure
I would not have got this project completed without the vast knowledge of experienced peers at my disposal. Referenced below are items that were specifically helpful with specific functional attributes of the solution.