Google Sheet It Helper
Automate Your Reporting to Google Sheets
Overview
This is quickly becoming one of our most popular Helpers and the reason is simple - flexibility and control. You see the reports built into Infusionsoft are, well, ugly and many times they don't give you the control you need over options, filtering or presentation.
Rather than reinvent the wheel by trying to predict what types of reports you would want and then creating canned (limited) reports, well, we took the complete opposite approach. Our intent was not to design a reporting system, but merely to bridge two great systems - Infusionsoft and Google Sheets.
If you haven't used Google Sheets yet, you should. You can do anything you could possibly imagine in terms of graphs, charts, pie charts, histograms, pivot tables and even SQL queries from your data sets. What that means is that you can do anything your little heart desires when it comes to reporting. Best of all, once you set it up, you can automate it, share it and distribute it to whomever you want - even if they don't have access to Infusionsoft.
Highlighted Features
- Realtime Data Sync
- Scheduled Data Sync
- Sync Any Saved Report or Saved Search
- Multiple Data Sources Per Sheet
Realtime Data Sync
There are multiple ways to make the data sync realtime. You can either manually refresh the data by running the script, or you can also set it up so that it refreshes automatically when you load Google Sheet document.
Scheduled Data Sync
Another popular option is to setup the data sync so that it is scheduled to run on a fixed schedule, like every Monday at 4am or something like that. This can be ideal if you are sending out daily or weekly automated reports and want to make sure the data is fresh first.
Sync Any Saved Report or Saved Search
Our Google Sheets Integration works with any saved search or any saved report in Infusionsoft. You can customize them, pick the columns you want, pick the fields you want, select the dates and options you want and then save the report. Then inside of MyFusion Helper you simply associate that saved report with a Google Sheets Helper. Then just copy and paste the supplied code into Google Sheets and you're good to go.
Multiple Data Sources Per Sheet
The other big benefit of this Helper is that you can create very sophisticated Google Sheets where multiple Helpers and multiple data sources from multiple reports are all combined into one Sheet.
The Toolbar
Above is the toolbar options you'll see for the Google Sheet It Helper. Here are the Toolbar button options from left to right:
- Copy Link for Infusionsoft - This first blue copy link button copy the URL used for the http post snippet inside of the Infusionsoft Campaign Builder.
- Configure Button - The last button on the far right is the start configuration button which allows you to View, Edit, Copy, Delete, view the Usage Report and the Usage Log.
Tips, Tricks and Shortcuts
Video Transcript
Hello and welcome to MyFusion Helper, my name is Troy Broussard. In this short video I’m going to show you how to use our Google Sheets integration. This is easily one of the most powerful features that we have. I’ve got to tell you, just this one feature alone more than justifies the entire monthly price of MyFusion Helper.
I’m going to show you how it works. This video is going to be longer than most because there is a lot of detail to go into and I really want to go through some different case studies and show you some practical uses of it. It’s more than just understanding the ABC of how it works, but really kind of thinking through how you’ll use this in your business.
It’s extremely powerful and we’re going to spend a little time going through it. I’m also going to show you how to set it up inside of Infusionsoft and back and forth what you need to do to configure it. Then we’re also going to switch over into Google Sheets and show you how to deploy it as well so that you can work within all of the environments and bring everything together. All right?
Let’s go ahead and jump right in. Unlike some of the other Helpers where you begin with configuring the Helper itself, with this particular Helper it’s really an integration between Infusionsoft and Google Sheets. For those of you that are not intimately familiar with Google Sheets, it’s essentially an online version of Excel. It allows you to do all kinds of data formatting and data manipulation.
The way we do this integration is by creating a saved search. There are lots of different ways for saving reports, it’s not just through the save searches. You can also do any type of reports. In this particular search I’ve got contacts that were created during the last 90 days. Maybe I’m looking at some contact creation or lead generation statistics. I’ve got this pop up here so I’m hiding the sensitivity of the email address, so I’m not showing all of that data here. Inside of this you know how you can edit the filters by searching all kinds of data here. You can go through and click on what type they are or address information. There are all kinds of filters that you can do here.
You are able to the configure these filters the way you want and then save the search right here. When you save it, that gives it a name and that becomes a saved report that we can access through the API. Another type of report that you could do, this would be an order search. We can come in here and view orders and then we can configure the options that we want and save that as well. Notice that the columns that are listed here are the exact columns that are going to be exported to Google Sheets.
So make sure that you set up these reports the way you want the data to be exported. If you don’t need the name and all you really need is an ID and a total, then that’s fine. If you want to look at totals that are under $500, then make sure and edit your criteria and set that up. However you save this report is going to generate the data into Google Sheets.
This is the first part of the configuration. I recommend using some type of a prefix like GS for Google Sheets or GS-. I spelled it out, Google Sheets, here is an abbreviation. You’ll get a lot of these reports in here, so it’s kind of nice to group them appropriately. I would probably use a capital GS, then a dash and then the name of what it is. That way all of my Google Sheet export reports would be logically grouped together. That’s the first part of what we’re doing, that’s just setting up the reports that we want to use with the integration.
The next part is to come into MyFusion Solutions itself and we’re going to create a new Helper. When we create that Helper, it’s going to be just Google Sheet It and we’ll give it a name. “GS,” I’m just going to do “Demo” here because I’ve already got the two set up that I want to use. Just to show you how this would work, it is so superbly simple. Right? The only thing you have to do is name it and then just select the search that you want to use.
I prefaced them with Google, so here’s the Google leads and the Google orders that I just showed you inside of Infusionsoft. That’s all you have to do. If I wanted this to be a lead to tie into this leads report that we have, I would just say, “Create Helper.” Now I’ve got that all set up and here you see the demo. I’ve already got these two set up and that’s what I’m going to tie in, so I’m going to go ahead and delete this. You see how simple that process is. All you’re doing is selecting the actual report and setting it up.
Now let’s go into Google Sheets and actually configure and deploy all of this so that you understand how to connect the dots from A to Z. Okay? The first thing you want to do is copy this link, this is our code link inside Google Sheets. So we copy that and then we’re going to come inside here into Google Sheets and the thing that we’re going to do here is a little bit different. We’re not going to work here for a second, we’re going to set some things up. We’re going to go into the Script Editor. When we go into the Script Editor, it’s going to launch this other window and pull in this code.gs file. We’ve already copied the code that we’re going to use, so now we just paste that code in and that is the code that’s going to execute and it’s going to go launch our API call and pull in the data. So we’re creating an integration function.
There are a couple of things that I want to walk you through here. You can change the name of this, we default it here, but I’m going to go ahead and change the name. This was our orders. I’m going to change the name to “Orders 90 Days,” just so I have a name that makes sense to me. There’s one other thing that I wanted to show you here that you may or may not need to change. It’s important that you see where it’s at. Inside of here you’ll see a sheet reference, a sheet one.
We assume that, when you set one of these functions up, it’s going to be putting the data right here in the upper quadrant, cell A1 of sheet one. If you want to change that and you want to put it on sheet two, then you would change that to sheet two here. Okay? If you wanted to change it from A1 to B3 is where it starts, you could do that right here. I’m going to leave it as A1 because we’re just going to put it up in the upper left quadrant, that’s the easiest and recommended way to do this. You’ll see this and I’ll give you some best practices as we go ahead and deploy it. Now that’s there. You notice that it’s not saved, so I’m going to go ahead and hit “save” and now that is saved.
One thing to keep in mind is that you may get a pop up confirmation saying that the sheet requires you to authorize access. If that’s the case, you have to click on the “allow” button to allow access from the data into the API. So make sure and confirm that if you need to. We’ve got this in here and I’m going to show you now that it’s already working. If I go here and choose “run” and I run this particular function that I just created, you’ll see that it’s going to give me a slight delay and then it’s done.
If I come back in here and I pull up sheet two, you’ll see that all of the data is here. There are a couple of things that I want to remind you of. Notice that we did change this to sheet two here, so that’s why it’s going on to sheet two instead of sheet one. It’s important that we set that appropriately. Then I go back here and I change this instead to sheet one, hit “save” and run that again to re-execute this. Then I go back into Google, you’ll see that it’s now on sheet one and the old data is still there on sheet two until I delete that. Okay? You’ll see that it brought over the data into sheet one. It’s going to go where you send it to go, so that’s important.
The last little thing I want to demo to you is if I do change that from A1 to C3 instead and save that, let’s go ahead and delete it from here first of all. Then we’ll go back here and rerun it. We’ll go in here and now you see that it offset the data and brought it here. Okay? The number of columns is going to be consistent every time you run it because that’s defined by the report as you set it up in Infusionsoft.
The number of rows is going to vary, so use that to your advantage and make sure that, when you set up your formulas, they’re taking into account the fact that the row data will vary. I’m going to go ahead and delete this, clear this out and put it back to a nice, clean A1 position in that upper left hand quadrant. It’s just where I like to do things. We’re going to go ahead and leave this on sheet two for the orders and we’re going to put the leads on sheet one. Let’s go ahead and do that and we’ll rerun it and have everything in there. Okay? Now we come back here and on sheet two in A1, everything is set.
Now we’re going to go into MyFusion Helper and we’re going to grab the leads. We’re going to put those on sheet one. As you can see here it was at six runs, that’s how many times the API had been executed before. If I hit “refresh” now you’ll see that’s jumped up to ten because in the demo I’ve actually pulled the data four different times, so it increments here. You see that as well. I’m going to go ahead and copy the link for the leads and we’re going to switch into Google Sheets.
We want to put this on sheet one, A1, so we’re going to come in here. I’m going to go to the end and hit a couple of carriage returns and then paste in the function code first. Remember I like to rename this, so I’m going to call this “Leads 90 Days.” Okay? I’m going to leave it on sheet one is where I want and it’s going to default to A1 for its reference point. That’s fine, we’ll go ahead and save it. Make sure and check the name because, if you’re using multiple on the same sheet, you may have to rename it so that you don’t have a conflict with the same name. All right. Let’s go ahead and save that now.
Now we can run and generate the leads as well. This one will take a little longer, there we go. Now you can see that it’s loading the data. Don’t worry if it takes a little bit of time to load. This actually has a lot of record that came back, there are several thousand record. While that seemed like it took a long time, for the amount of data that came back it really didn’t. There are over 3,000 or 4,000 records here as you can see. Tons of data came back in that time, it’s actually quite fast with the API integration. That is the basic integration of how this would work.
The next step is in how to set this up so that the data is pulled automatically. This is step two. All right? Now step two in pulling in the data is to go here into the Script Editor again. Now we’re going to go into resources and triggers. It says, “No triggers are set up. Click here to add one.” All right. Notice that it has the drop down for the functions that we’ve created to get the data. Then we can set up events to when to go and automatically run and pull that data. I’m just going to set it up as a day and I’m going to set it up to run at maybe three in the morning each night. Then I’m going to go ahead and add a new one for my leads to do the same thing.
I’m going to set up a day timer and I’m going to have it update at 4:00 am – 5:00 am just to be at a different time. I would go ahead and save this, I’m not going to because I don’t need to set this up it’s just a demo. All you have to do is hit “save” now and this will automatically pull the data in through the API from Infusionsoft on an automatic basis for you. Okay? Please do not configure these to be more frequent than you need them.
If you set this thing up to pull every five minutes and we see exorbitant API activity on your app, then we will have to disable it and reach out and contact you. Please only set these timers up with the frequency that you need. Many times daily is all you need or even weekly or monthly, depending on what types of data and reports you’re looking at. Okay? I’m not going to save these because I don’t need them to be as triggers, but that is how you set up the automation behind this.
What you’ve done you never have to redo. Once you set up the automation and once you set up the functions, that’s all done and you won’t need to go back into that Script Editor anymore. Now we can come in here and we can see our data. What I wanted to show you is a more practical example. What I’ve done is I’ve created a chart off of this sheet two data. This is a histogram of the order data. If we go here and we look at our orders you can see the order amounts here, they vary in various price points.
Here’s up to almost $1,100, here’s down to just $99. This histogram chart is going to show us where the majority of our orders are. The majority of them are at this $375-$400 price point right here. Okay? Excuse me, I said that the wrong way. This is the volume, so there’s about 375-400 orders at this $245-$255 price point. As you see some of the higher price points, here was that one at like $1,000. There are only a handful of them here. This histogram shows you where the vast majority of your orders are. There are a bunch at $100, etc. in this loop.
Now to show you how this works real time, I’m going to go in and modify the order report that we’re pulling. We’re pulling off of these orders from the last 90 days. Again if we note here we had some going way up in volume and we saw price points all the way up here at $1,000, I’m going to turn around and put a filter on this. I’m going to say that the order is less than $500. Now I’ll make sure and save it, so we’ll click here to “save.” Now it has updated this page. If we go into sheets nothing has changed yet because we haven’t re-pulled the data down. We haven’t refreshed the data yet.
If you’re looking at these reports the next day, then we would have had that published in the night and it would have pulled it down automatically. What I can do is I can go back into the Script Editor and I can just run that function to update the data. So I can go ahead and rerun the order data here now. Now when I come into the histogram, boom. Look it there. It just automatically real time updated. This is the beautiful power of this functionality. Right?
You can set up all kinds of charts, any type of forms or calculations or formulas that you need to lay out the data in any manner that you need to see it for your different accounting departments, exports or anything that you need to do. Then you simply update the data on the frequency that you want. What I recommend to keeping this organized is to have your rod data on separate sheets than your charts and your formatted data so that you’re not mixing and matching the two on the same. It kind of gets cumbersome and is easy to make a mistake or to override a sale that you don’t want to. The easiest way to do this is to keep the data separated from the reports and the formatted version of that data. That is the power of this integration.
To recap what we did, step one is we set up a sheet. We went into Google Sheets and we created that sheet one, set it up clean and got everything ready. Step two was we went ahead and deployed the code from the code GS from the app. We copied that and we deployed it into the Script Editor. Step three then was to set up the scheduling of your data refresh. This is really up to you on the scheduling cycle that you need. Remember, do not do this more frequently than you need. We do monitor the usage of the app aggressively. If you’re setting something up to pull down every five minutes, we’re going to reach out to you and have a chat. We can’t abuse that because that’s going to affect everybody else in the system. Make sure to just pull down the data that you need at the frequency that you need.
We have a couple of questions that come in about limitations on this. There are only a couple of limitations that you have to keep in mind. First of all, we will pull as many records as we can. What I mean by that is the API limits you to 1,000 at a time, so people think, “Well, then you’re only pulling 1,000 records.” That’s not true, we’re going to pull several thousand records. As you saw in the examples, they had 3,000/+ records being pulled at once, so we’re not limiting on 1,000, 10,000 or anything like that. There’s no hard cut off number.
There is, however, a number that is on the side of Google that is a limitation, that number is 10 megabytes. The problem is it’s very hard to understand what does 10 megs of data look like. It could be lots of rows with only a couple of columns, or it could be less rows with a lot of columns. Right? We don’t have any control over that. If you’re pulling back really large data sets that are larger than ten megabytes, the Google API import restricts that and we won’t have control over that. I recommend that you’re pulling smaller reports than that. But, as you saw, I pulled down several thousand records with no problems whatsoever. This is probably only going to be a problem if you’re trying to export all of your data or something like that. If you’re working with reports you shouldn’t have any issues with that whatsoever. Okay?
All right. Hopefully this gave you some great ideas and some examples. I wanted to talk about a couple more situations, but one of the things that our clients are using this for is automating data exports for accounting. There are individuals in your Accounting Department perhaps that need to see the data formatted in a certain way. You can set that all up once and create a nice spreadsheet inside of Google Sheets and then have the data real time fed from Infusionsoft. This is one particular type of usage.
Another one is creating snapshot charts that reflect your business metrics and goals that are automatically updated. Either looking at that yourself if you’re a solo entrepreneur or forwarding that on to the CEO of the company where he or she can look at that data and not have to even get into Infusionsoft. They can just see the core metrics of sales, repeat sales or whatever you’re tracking that are the core business growth metrics. It does that with nice charts and graphs, however you want to lay that out.
Another good example is just honestly any kind of reporting you need. At the end of the day you saw how incredibly flexible this is. You can really configure this to do anything your little heart desires. There are so many possibilities, this is by far the most powerful integration that we’ve offered and customers love it. This is one of the most widely used and enjoyed features of MyFusion Helper, so I hope you’re enjoying it as well. If you need help, reach out to us on the Help Desk at help@myfusionsolutions.com.