Workforce management with Google Sheets, MessageMedia & C#

If you store your business data using spreadsheets, there are things you can do using the MessageMedia APIs to automate some of your business processes and make your life easier. Gone are the days of sending around copies of spreadsheets to support staff, who manually copy and paste your data into secondary programs. You can use the MessageMedia APIs to populate your messages directly from your data sheets hosted in the cloud.

In this example, we’ll pull in your business data from a Google Sheets spreadsheet. Using Google Sheets is a great way to collaborate on your business data management with other people in the organisation, as it allows multiple people to edit at once. Rather than one person having a spreadsheet that has to be on the same computer running your application, you can make a Google Sheet.

For this implementation we’ll be using Workforce Management as a use case; both shift reminders and timesheet reminders. You will use shift and personnel data on Google Sheets, to send out roster information to all your staff, then you will follow up with a reminder to fill in their timesheet. With the added efficiency gains of using Cloud-based Sheets and API integration for operations automation, you will be giving yourself hours back in your day.

Shift reminders

SMS shift reminders/notifications are a cost-effective way to notify employees of shift without them having to come in to view the roster somewhere. This works especially well for intermittent workforces with casual employees. SMS has a much better open rate than email and is far less labour intensive than phone calls. Research shows that 90% of messages are read within 90 seconds of receiving it. This is a much better solution for important time-sensitive business communications, as opposed to having your email potentially spam-boxed, and left unseen. Using an SMS solution, all you have to do is work out your scheduling, and then run your program to send all the reminders.

Prerequisites

In order to work through both the tutorials, you need:

Installing dependencies and setting up your app

As of the publication of this guide, we now have a shiny new v2.0 C# SDK that is .net compatible. We’ll be using this SDK in Visual Studio. Following the guide on the SDK readme, add a console app to your solution explorer and call it “ShiftRemindersApp”.

The only dependency aside from the MessageMedia SDK is the Google Sheets API Nuget package. Google has a great quick start guide for using this package in a .net application here.

When you’ve installed the dependency as per Google’s instructions, and created your spreadsheet, you’ll need to fill it with data. The below is an example of the kind of data sheet we’ll need, with at a minimum a number (in international format), a start time and a name.

ID Name Mobile Start Location
1 Claire +614<number> 10:00 Brunswick, Melbourne
2 Mike +614<number> 10:00 Brunswick, Melbourne
3 Jennifer +614<number> 16:00 South Yarra, Melbourne
4 Montana +614<number> 21:00 CBD, Melbourne
5 Tristan +614<number> 21:00 CBD, Melbourne

 

Accessing the data

Now that you have everything set up, you can start writing the code in the Program.cs file of your App.

Inside the Program level of the file add some class parameters:

// If modifying these scopes, delete your previously saved credentials

// at ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json 

static string[] Scopes = { SheetsService.Scope.SpreadsheetsReadonly };

static string ApplicationName = "TimeSheet Appt";

This just gives your App a name and sets up the constant to declare the scope (we only need read-only at this stage).

Then inside the  “static void Main(string[] args) “ function, add the code to read in the credentials, and generate an access token:

// Read the Google API credentials

            UserCredential credential;<
            using (var stream = new FileStream( "credentials.json", FileMode.Open, FileAccess.Read))

           {
              // The file token.json stores the user's access and refresh tokens, and is created
               // automatically when the authorisation flow completes for the first time.

string credPath =   "token.json"  ; 

              credential = GoogleWebAuthorizationBroker  .AuthorizeAsync( 

                  GoogleClientSecrets.Load(stream).Secrets,
                  Scopes,
                  "user",
                 CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);

         }

Now you can create a Google Sheets API service and read all the data. When you create a Google Sheets spreadsheet and get the public share link, everything between the “/d/” and “/edit” is the spreadsheetid, which you will need to sub into the code below. For example

https://docs.google.com/spreadsheets/d/1tUEdWwA-cGjVUD_0O2Rk_AFQcN-Sv9w4p7y72g326Rw /edit?usp=sharing

 // Create Google Sheets API service. 

            var  service =   new   SheetsService  (  new   BaseClientService  .  Initializer  () 

          { 
              HttpClientInitializer = credential, 
              ApplicationName = ApplicationName, 
          }); 

            // Define request parameters. 
            String  spreadsheetId =   "Your Spreadsheet ID"  ; 
            String  range =   "Reminders!A2:E"  ; 
            SpreadsheetsResource  .  ValuesResource  .  GetRequest  request = 
                  service.Spreadsheets.Values.Get(spreadsheetId, range); 

            // Prints the names and majors of students in your spreadsheet 
            ValueRange  response = request.Execute(); 
            IList  <  IList  <  Object  >> values = response.Values;

 

Adding in the messaging logic

Now you have everything you need to send a group of messages using the new c# SDK. Immediately after all the previous code, in the Main function, add this:

            // Create the MessageMedia client 
            MessageMediaMessagesClient  client =   new   MessageMediaMessagesClient  (); 
            MessagesController  messages = client.Messages; 

            // Set up the message request body 
            SendMessagesRequest  body =   new   SendMessagesRequest  (); 
          body.Messages =   new   List  <  Message  >(); 

            if  (values !=   null  && values.Count >   0  ) 
          { 
                Console  .WriteLine(  "Number, Time"  ); 
                foreach  (var row   in  values) 
              { 
                    string  name = row[  1  ].ToString(); 
                    string  time = row[  3  ].ToString(); 
                    string  number = row[  2  ].ToString(); 
                    string  location = row[  4  ].ToString(); 
                    Console  .WriteLine(  "{0}, {1}"  , row[  2  ], row[  3  ]); 

                    Message  body_messages_0 =   new   Message  (); 
                  body_messages_0.Content =   $"Hi   {name}  , \n  Your next shift is at   {time}  -   {location}  "  ; 
                  body_messages_0.DestinationNumber = number; 
                  body.Messages.Add(body_messages_0); 
              } 
          } 
            else 
          { 
                Console  .WriteLine(  "No data found."  ); 
          } 

            try 
          { 
                SendMessagesResponse  result = messages.SendMessagesAsync(body).Result; 
                Console  .WriteLine(result); 
          } 
            catch  (  APIException  e) 
          { 
                Console  .WriteLine(e.Message + e.ResponseCode + e.HttpContext.ToString()); 
          }; 

This adds each entry as a message in the messages array and uses string interpolation to populate the message content with the other pieces of relevant data.

This full gist for this part of this app is here.

Running the code

To run this program and test it out, you now just hit the run button in Visual Studio. This will open up Terminal and print out all the records for you before sending them out:

When you run this for the first time it will open up and Google OAuth workflow in your web browser to create the token. After you’ve authorised the app the token will be saved in token.json (you will see it appear in your project explorer) and you won’t have to generate it again each time.

timesheet reminders

Now that you’ve set up shift reminders you can easily do timesheet reminders by adding an extra field to the same spreadsheet. For example:

ID Name Mobile Start Location Filled
1 Claire +614<Number> 10:00 Brunswick, Melbourne Yes
2 Mike +614<Number> 10:00 Brunswick, Melbourne No
3 Jennifer +614<Number> 16:00 South Yarra, Melbourne No
4 Montana +614<Number> 21:00 CBD, Melbourne Yes
5 Tristan +614<Number> 21:00 CBD, Melbourne No

 

Now you can simply copy all the code from your previous app, and edit the bits you want so you can send timesheet reminders based on whether they have filled it out already on not:

            // set up the message request body 
            SendMessagesRequest  body =   new   SendMessagesRequest  (); 
          body.Messages =   new   List  <  Message  >(); 

            if  (values !=   null  && values.Count >   0  ) 
          { 
                Console  .WriteLine(  "Name, Filled"  ); 
                foreach  (var row   in  values) 
              { 
                    string  name = row[  1  ].ToString(); 
                    string  number = row[  2  ].ToString(); 
                    string  filled = row[  5  ].ToString(); 
                    Console  .WriteLine(  "{0}, {1}"  , row[  1  ], row[  5  ]); 

                    if  (filled ==   "No"  ) 
                  { 
                        Message  body_messages_0 =   new   Message  (); 
                      body_messages_0.Content =   $"Hi   {name}  , \n  Please make sure you fill in your timesheets."  ; 
                      body_messages_0.DestinationNumber = number; 
                      body.Messages.Add(body_messages_0); 
                  } 
              } 
          }

The full gist of this version is here.

If you run this code from the same project you should already have a save token. If you’ve run it from a new one it will create a new authorisation token after you go through the authorisation workflow.

Conclusion

The best part of having this on a Google Sheet is that you can make another Google Sheet for the time sheet, which also updates the “filled” field on your timesheet app. Running your business using cloud software has many benefits for efficiency and automation. You can also use Google Sheets code to run such automation directly from your spreadsheet. In this developer guide we’ve shown how easy it can be to use your existing business data, to send out shift notifications and then chase up timesheets. This process can be extended to many other business applications. Let us know what you’re using Google Sheets for. You can find us on the developer Slack channel.