Automating Workflows > POS Data to Google Sheets using an Apps Script

Point of Sale systems are ubiquitous in bars and restaurants, but don’t always integrate neatly with your workflow. Using a Google Apps Script, we can automatically populate a Google Sheets spreadsheet from the daily email our POS sends.

This will be Part One in this series. In Part Two, I’ll demonstrate how you can use Zapier to perform the same action with a lot less code (but with a monthly fee). This is geared towards bar & restaurant owners, so I’ll try to be as detailed as possible. Fair warning that this is a long post.

The complete code for this blog is available on my GitHub here.

Why though?

At Jupiter Disco we use a SmartTab POS system that we’re generally happy with because it’s highly efficient during service and intuitive to use. Opening and closing tabs is a breeze and the built-in search function for products and tabs is really well executed. Like any POS system, it has some drawbacks, one being that it doesn’t integrate with any of our back office workflow, namely Google Sheets.

Every day we have to pull up the report from the previous night and copy and paste the individual values into a Google Sheet in another tab in our browser (or worse, just type it in, leading to input errors). And we’re certainly not the only ones performing this archaic workflow, as many, if not most, independent bars and restaurants will execute some form of it on a daily basis.

It doesn’t take long, but it can lead to errors that later have to be tracked down. And it just feels unnecessary. So let’s automate this simple task first, and then we can tackle some more complicated automations later.

Are we sure about this?

No. Look, every POS system is different. They all run different reports, name the same value something different (“Net Sales” or “Net (Taxable) Sales”), send different emails with different layouts if they send emails at all, so there is no one size fits all solution here. I’ll do my best to work through the process, so you can apply to your own POS and workflow as necessary.

JavaScript, which Google Apps Script is built on, is not my native language, so buyer beware. This whole post is essentially information I pieced together from sugar packets.

I’ll walk through the process of setting up a Gmail-to-Google Sheets-spreadsheet automation from a Square Daily Sales Report Email (which we use as a back-up payment processor in case our primary merchant services provider goes down). The code for a SmartTab implementation is available here from my GitHub.

Technical Requirements

I’m assuming the following:

• You get a Daily Sales Report email from your POS system.
• That email is sent to a Gmail account.
• That same Gmail account is associated with a Google Shared Drive where you keep all your bar / restaurant related work.

Step_1 :: Set up a google sheets file

You probably already have a spreadsheet to track sales, so our first step will be to create an intermediate spreadsheet that we’ll dump the parsed data into. My recommendation is to (a ) Create a new folder in your share drive — call it “pos_sales_data” or something, (b ) Create a new Google Sheets file in that folder, (c ) Name the first worksheet “sales_data” and (d ) Add a second worksheet called “test_data”. Finally, you’ll want to (e ) add your header row to the “sales_data” worksheet — these are the columns you’ll want to populate from your POS: Date, Gross Sales, Discounts, Sales Tax, Net Sales, Etc. Assuming that you’re following along, your spreadsheet will look something like this:

Step_2 :: Connect & create a Google Apps Script

You most likely haven’t already connected Google Apps Script, so the next step will be to navigate to the “+ New” button in your Google Drive, go down to “More” and then click “Connect More Apps.”

Once there, search for “google apps” and click on the connect button for “Google Apps Script”:

Next, create a new Google Apps Script by click on the “+ New” button again and navigating to the application. It will open in a new tab and should look like this:

Name your project whatever you want and let’s get to the fun part.

Step_3 :: Identify relevant emails

Navigate to Gmail and enter a search term that finds only the Daily Sales Reports your POS sends. For example, it may look something like this:

“from:noreply@messaging.squareup.com AND Daily Sales Summary Report”

Copy the complete search term and navigate back to your Google Apps Script.

Step_4 :: Start coding!

First, name your function. Instead of myFunction let’s call it parseEmailPOS. Next, create a variable searchTerm and assign it the string of the search term we used above to identify the relevant emails with the following code block:

var searchTerm = "<INSERT_YOUR_SEARCH TERM>"

Then create a variable thread that will hold the first thread (which we identify with the [0] index) in the array of threads returned by the built-in GmailAppsearch method.

After that we can find the first message in the first thread and assign it to the variable message. To test our integration, pass the argument message.getSubject() into Google Apps’ built-in Logger method. Your code should look like this:

Next, go to Run -> Run function -> parseEmailPOS and click.

If this is your first time running this script, you’ll likely get the following pop-up:

Click Review Permissions and follow the instructions to allow the connection to your Gmail. You want to make sure you’re adding the correct user here — the user that receives the Daily Sales Report from your POS system.

Once this is done you can run the script, and assuming there are no errors, navigate to View -> Logs to confirm that your script was able to find and log the Subject Line of the most recent email sent by your POS:

If you see something similar to this it means your script worked! Nice.

Step_5 :: Connect to your google sheets spreadsheet

The goal here is to parse that email and send the data to a spreadsheet, so next we need to connect to the Google Sheets spreadsheet we made earlier. The process is similar to what we did for Gmail except now we’re using the SpreadsheetApp class in Google Apps Script. The code will look like this:

Copy and paste the above inside of the parseEmailPOS function (copy it inline with the previous code, inside the { }. Inside of the var url string, replace <YOUR_KEY_HERE> with the key from your pos_sales_data Google Sheets spreadsheet that you made earlier (or just copy and past the entire url and replace the string if it’s easier).

This code instructs the script to open the spreadsheet, find the appropriate sheet, and then append a new row to that sheet consisting of our Subject Line. Don’t forget the [] surrounding message.getSubject() in your appendRow argument, as the method is expecting an array.

Same as before, run this function by navigating to Run -> Run function -> parseEmailPOS. You’ll be asked to authorize the connection to the Google Sheets spreadsheet. This time, if the script ran successfully, you should be able to go to the spreadsheet in another tab, click on the test_data worksheet and see the subject line:

Taking a step back, our little script has now successfully searched for and returned the most recent email from our POS system, and input the subject line into a Google Sheets spreadsheet. Not bad!

Step_6 :: Prep the email

These next couple of steps are difficult, but we can break them down into small steps and work through it fairly easily by running the script often and debugging as we go.

First, let’s comment out testData.appendRow([message.getSubject()]) by adding // in front of the line. Then let’s add some code inside our function that will create an array of lines of HTML:

messageBody is a String of HTML. We run the .split("\n") method on it to create an array of lines of HTML that we assign to line_array. Then we loop through line_array and append each line of HTML to a new row in our test_data worksheet. Why are we doing this? So we can easily identify where the pertinent information is buried in the HTML of this email.

As far as I could find, there is no good way to parse HTML in a Google Apps Script. We can’t import BeautifulSoup and cleanly work our way through the document. So we’re basically going to hack together a solution that works for each specific use case, that being the exact email that your POS system sends you. Since they’re sending a structured document, and not some random email, as long the format of the email stays the same, this hack will work. If it changes, you’ll just have to retrace some of the steps we’re about to take.

Before you run the above code, clear your test_data worksheet. It should be completely blank. Then run your parseEmailPOS function and navigate to that worksheet. It should look like this once the function finishes running (it could actually take a minute or so):

Neat. What we’re looking for next are the actual data points we want to pull from the HTML, identified by which row in the spreadsheet it appears. Every POS will actually vary wildly at this point, so let’s take this line item by line item.

Step_7 :: Parse the email

We’re going to create a new variable for each data point that we want to extract from the email and input into our Google Sheets spreadsheet.

To get the full Date from the Square email, we actually want to parse the Subject Line, so we can knock that out with the following code:

var reportDate = message.getSubject().split(“ for “)[1]

Again, this is a bit hacked-together — the code is *very* hard-coded. But as long as the subject line doesn’t change, we now have a variable that holds a value in a format Google Sheets will interpret as a date.

If you want to see this in action, comment-out the “Identify Relevant Line Number” section so we don’t have to wait for that to run again, and add a line inside the function to append a row to the main sales_data worksheet:

salesData.appendRow([reportDate])

The full function up until this point should look like this:

And if you run it, and navigate to your spreadsheet’s sales_data worksheet, it should look like this:

Next we’ll work through the rest of the data points:

business_day is easy, and a little redundant, but we can pull it from line 213 in our test_data worksheet, which is number 212 in our line_array index:

var BusinessDay = line_array[212].split(“>”)[1].split(‘ ‘)[0]

.split works by separating the String on the string that we pass as an argument (e.g. “>”). In the above code block we actually split it twice, first on “>”, then on a space.

Now we can populate all of the values. For the Square email, this is actually pretty easy since they’re not buried inside of HTML tags, but we do need to delve fairly deep to find them all. For example, the Gross Sales dollar amount is found here on line 300 (index 299 in our array).

So our variable will be:

var adjustedGrossSales = line_array[299].split(‘$’)[1]

By now you should have a good sense of the methodology for finding and extracting each value:

• Identify value in test_data worksheet and note row number (e.g. 300)
• Create a variable following this format: var posValue = line_array[row_num — 1].split('$')[1]
• If your posValue isn’t a standalone value, you’ll have to add some additional code to break it out. Try adding another .split('<whatever_comes_next>')[0] to the variable.
• If you have whitespace around the value, append .trim() to the end.

The full set of variables, along with our appendRow, should look like the following for our Square email:

Run this function and your sales_data worksheet should fully populate, depending on which columns you included. If you have additional columns you’d like to include you’ll have to add the variable. Note that appendRow populates the columns in order of the variables in the array, so keep your order straight.

Some quick notes on the Google Sheets component of this:

• appendRow appends new data under the last populated row in the spreadsheet, so you can copy and paste all your historical data into this spreadsheet if you want to create a sort of primary sales worksheet that you pull data from in all your other spreadsheets.
• You can also just feed this worksheet into one in another spreadsheet file using IMPORTRANGE() and then using a VLOOKUP() or another function to automatically populate your target spreadsheet.

Step_8 :: Add index validation

We want to include a basic test to give us some comfort that the values we’re extracting from the HTML are the correct ones and haven’t changed, so using my limited knowledge of JavaScript, let’s wrap the above variables and appendRow method in an if / else statement that confirms that a couple rows are where they’re supposed to be, and throws up an error otherwise:

Step_8 :: Refactor

I’ve kept everything in our one function parseEmailPOS up until now, but it’s bad practice to wrap all this action in one function, so let’s refactor the code, and add some enhanced functionality along the way, namely a check that the date doesn’t already exist in our Spreadsheet (this is especially important for a use-case like Jupiter Disco’s with Square — we only get reports from Square when we need to use it as a back-up, so when we run it on a daily basis it would just keep repeating the same data from the same email).

If you’re following along, I won’t get into the details of every element here — it’s mostly copy and pasting our existing code into new functions and then passing the return from those functions into our existing ones.

getMessage() will pull the most recent email from our POS system:

getMessage()

connectSpreadsheet() will connect to our Google Sheets file and return a Spreadsheet object.

connectSpreadsheet()

appendHTMLarray() isolates our HTML dump into the test_data worksheet, so we only have to call that function when necessary.

appendHTMLarray()

getLastDate() is an annoyingly complicated function that returns the most recent Date in our Spreadsheet (assumes Date is Column ‘A’). Adapted from this Stack Overflow post.

getLastDate()

parseEmailPOS(), our main function, checks the getLastDate(), then checks that the HTML elements are all where they’re supposed to be, before appending the new row. If the date already exists, the script will log it via the built-in Logger method. I’ve also moved all the variable assignments outside of the nested if/else blocks.

parseEmailPOS()

Our POS specific code — where you’ll use the appendHTMLarray() function to identify where the relevant HTML elements are located and assign to variables, is commented, and contained to this function.

If anything in your code looks off, please refer to the code on my GitHub page for this project.

Step_9 :: Add a time-based trigger

Right now the function runs when we navigate to Run -> Run function, but we want it to run automatically every day when this email hits your inbox (for example, our SmartTab email hits at 7:00 AM EST). This is super easy to set up in Google Apps Script.

For reference, we’re just going to follow the “Managing triggers manually” Google Apps instructions here:

• Navigate to Edit -> Current project’s triggers
• Click +Add Trigger in the bottom right

You should get a screen like this:

Next, choose which function to run: parseEmailPOS.

Leave the deployment on Head, and event source as Time-driven. Set the type of time based trigger to Day timer and then select the hour interval appropriate for your email (I have our SmartTab interval set for 7am to 8am). Click Save and you’ll be all set.

The End

Let me know in the comments if you have any questions or issues implementing this system. I’ll try to add additional scripts to my GitHub for other POS systems as well. Good luck!