At WSI we are always on the lookout for solutions that will help our clients get more value from the digital marketing campaigns we deliver. Measuring the results, not just in terms of number of leads, but also quality of leads, is an area that we’ve recently spent a lot more time working on – and unless clients have an established cloud based lead management system that’s well integrated with their website, it’s not always so straight forward to get that feedback… enter Zapier and Google Spreadsheets.
Liana Ling, a particularly helpful consultant in WSI’s global network of digital marketing consultants, recently introduced Zapier to us and I keep seeing more and more opportunities to work with this tool. What is it – in simple english? I see Zapier as a very powerful tool that helps online applications talk to each other and share data effectively. In a marketing and sales context, it can quickly translate into no more re-capturing of leads, no more typos, no more playing “broken telephone” (sorry to spoil the fun :-)), much better measurability, the list goes on and on.
Having spotted the opportunity, we set out to test if we could unlock some of this value for our clients. Here is what we wanted to achieve:
Use Zapier to add all leads submitted on the client’s website into an “All leads” Google Spreadsheet. Zapier connected Gravity forms and Google spreadsheets seamlessly.
Filter the leads by province into separate “Leads by Province” sheets, using Google’s Filter function.
Pull each province’s leads into a separate “Province” worksheet, so that the sales team’s at each of our client’s branches will only have access to the leads coming from their specific province.
Sounds simple enough, and it really is thanks to Zapier, until you get to step 3. Here is the essence of the email I sent to Zapier and StackExchange, asking for some help:
New entries entered into the “All leads” spreadsheet by Zapier, are not pulled through to the separate “Province” worksheet by Google’s standard ImportRange() function… unless there is some form of human action on the “All leads” sheet. In other words, I need to physically make a change / edit to the “All leads” sheet before the new entries are detected and pulled into the relevant “Province” sheet.
To me, it seemed that when Zapier inserts the new row of data into the “All leads” sheet, the ImportRange() function did not detect it was edited and therefore did not check for new records. I wanted to know if there was some kind of edit function that gets triggered by a human user but not by an entry from Zapier. Zapier came back quickly enough (thanks!) confirming that this indeed seems to be the case, and that they had found a way to make Google think the sheet was human edited, but it was over to Google to pick up on this issue and to implement it.
Where to next… time to find a trusty Google script expert. To cut a long story short, here is the solution we finally came up with:
Add the following script to your destination spreadsheet – the sheet you want the data pulled into:
function DynamicImportRange() {
var values = SpreadsheetApp.openById(‘Enter-All-Leads-Sheet-ID-here’).getSheetByName(‘Leads by Province’).getRange(‘A:H’).getValues();
return values
}
function getTempSheetId()
{
var tempSheetId = ‘Enter-Province-Sheet-ID-here’; //test
return tempSheetId;
}
function importR()
{
var tempSheet = SpreadsheetApp.openById(getTempSheetId() ).getSheets()[0];
ClearSheet1();
Utilities.sleep(10000);
ClearSheet2();
Utilities.sleep(1000);
tempSheet.getRange(1, 1).setFormula(‘=DynamicImportRange()’);
}
function ClearSheet1()
{
var tempSheet = SpreadsheetApp.openById(getTempSheetId() ).getSheets()[0];
tempSheet.getRange(“A:H”).clear();
tempSheet.getRange(“A:H”).setValue(“Loading…”);
}
function ClearSheet2()
{
var tempSheet = SpreadsheetApp.openById(getTempSheetId() ).getSheets()[0];
tempSheet.getRange(“A:H”).clear();
}
Note, the 2 x sheet ID’s and 1 x sheet name must be changed as indicated and the [0] must be changed depending on the sheet number you want the data pulled into. If it’s the first sheet, leave as is.
Setup a trigger for the importR() function. In the Script editor, go to Resources > Current triggers. Setup trigger for every minute, or 5 minutes, depending on your needs.
Enter the function =DynamicImportRange() in cell A1 in the “Province” sheet.
The above steps will flush the “Province” spreadsheet every 5 minutes and pull in all the data again, including any new entries.
Where to next… let’s hope Google sorts this issue out so we don’t have to resort to custom scripts indefinitely. In the meantime we are also experimenting with the use of Zapier to integrate our web forms with PipeDrive… which will add even more measurability to the sales team’s activities.
Hope this helps anyone else that is facing the above issue. Share it by dropping a link to this post where relevant.
