Copper CRM integrated contact us/sign up form for your website using Google Forms

Copper customers frequently ask us about setting up web forms that will automatically create a person (or lead) record and send a personalized email response upon submission.

Copper has posted a solution based on Wufoo and Zapier but it doesn't send an automatic email reply. It's also simpler and cheaper to use free Google Forms plus free Mailchimp, so we built it!

We loved this solution so much, we opted to use it on our own website.

Our live Contact Us Google Form: http://www.interlockit.com/Home/contact-us

If the email address submitted does not already exist in Copper, a Lead record is created instantly and a welcome email is sent using Mailchimp's Automated Welcome Email feature:

If the email address is found the phone number submitted is compared and updated if needed.

In both cases a Copper CRM Task is created to followup and is assigned to the user of your choice, plus Mailchimp sends a personalized welcome email.

You can even use Mailchimp to schedule automatic follow-ups, which is what we did for our Blink Reports Software Free Trial Sign-ups. This is another Google form we integrated to Copper CRM and to our Blink Reports for Xero Accounting software running in Python on Google App Engine/Google Cloud Platform. Mailchimp handles DRIP marketing to our free trial signups with a scheduled series of helpful emails.

In the case of the Contact Us Form 100% of the code runs inside the Google Cloud using Google Apps Script. The benefits are speed, simplicity, reliability, and security. It's all managed by Google's massive Cloud Computing resources and the world's best security team. All security for editing the online form and reviewing past form submissions in Google Sheets is managed by your existing Google Account logins.

Here's a code excerpt for the techies like me:

So what does it cost?

• It's FREE if we can become your G Suite license Reseller for 10 or more users, which is at no additional cost to you.
• Otherwise cost is $10 per month plus a small setup fee of $99 with discounts available for buying less than 10 G Suite users through us
• Mailchimp is free for up to 2,000 subscribers or $10/month for unlimited emails

Certainly reach out to us at http://www.interlockit.com/Home/contact-us to sign up or send us any questions you may have!

Automating Quickbooks from the Cloud

For all the benefits working in the cloud provides, sometimes migrating every aspect of your business operations to cloud services is not an option.  What do you do when your CRM and Project Management are cloud based, but you need to move data back to Quickbooks on the desktop?

One answer: get in touch with Interlock IT.

Our client had already switched their Contact and Project management to Norada's Solve CRM when they did just that:

I would like to integrate the Solve CRM API with Quickbooks to automate our workflow bidirectionally between Solve CRM and Quickbooks. We use Quickbooks Enterprise Construction Edition.

Our first response was to rule out other options, couldn't we move accounting into the Cloud? Specific features of the Quickbooks Contractor edition were mission critical; there was no direct cloud replacement. Xero, Quickbooks Online, and Freshbooks would not meet their needs at this time.

Enter the Quickbooks Web Connector, a legacy application released by Intuit, the makers of Quickbooks, designed to allow desktop editions of Quickbooks to communicate with web-applications, also known as the Cloud!

Armed with a method of communicating with Quickbooks on the desktop, we dug into the clients specific needs and developed the solution below.

Setting a Revenue Opportunity to "Won" in Solve CRM kicks off the process.

When a revenue opportunity is marked Won in Solve CRM, the following occurs automatically:

• Instantly create a Customer and Job in Quickbooks with details from the Solve CRM Company record.
• Add an Estimate to the Job and convert it to a Sales Order, using details from the revenue opportunity.

Details from Company record and Opportunity are synced into Quickbooks.

Now the accounts team can take over and work with the project in Quickbooks, tracking progress and financial details on the automatically created job in Quickbooks.

Finally, our system syncs financial report figures back into Solve CRM, allowing for reports on project finances to be generated entirely from data in the Cloud, avoiding a time consuming manual process of matching Quickbooks reports with Solve CRM Opportunities.

Later, Quickbooks report values are synced back into Solve CRM automatically, simplifying project based reporting.

The technology stack used to implement this solution consists of Google Apps Script and Python on the Google App Engine.  Webhooks triggered from within Solve CRM call out to a Google Apps Script living on Google Drive.  The Apps Script processes the Webhook and determines the required action. If Quickbooks related actions are required, the Apps Script passes the request onto the Google App Engine application, which handles SOAP based communication with Quickbooks, using QBXML.

The Quickbooks Web Connector polls the App Engine application and consumes any new actions that have been passed from Apps Script, returning results to the App Engine. The App Engine then feeds data back into Solve when necessary.

The end result is an integrated solution that saves time, reduces errors, and provides staff access to important financial data direct from Quickbooks!

Try Solve CRM for Free.

Customer case study: Google Apps and Xero work together to drive efficiency

At InterlockIT.com, we're Google Apps Resellers with a CPA on staff who noticed that there was a distinct lack of integration between accounting software (specifically Xero) and the wonderful collaborative features built in to Google Spreadsheets. We wondered if there was a way to maybe get the two to work together...

We built our powerful Xero reporting engine called Blink Reports to give users a faster and easier way to produce financial spreadsheets and reports from their accounting data. What we've learned along the way is that we can do so much more with the new Google Spreadsheets and Xero than we ever imagined was possible.

TargetCW is a leading provider of contingent workforce services across the US and overseas. Their corporate Kaizen philosophy of continuous improvement applies to their accounting and financial processes too. Xero recommended that Ryan Anning at TargetCW test out our Blink Reports for Xero reporting engine to overcome the limited number of columns that are normally shown on the profit and loss statement. Ryan's goal was to produce a revenue and cost analysis by worker which requires a report with more than a few hundred columns! It turned out that his need was so unique that initially our Blink Reports engine would produce an error due to waiting too long to receive the data from Xero.

On a standard profit/loss report, it's unlikely that you'd need such a wide spreadsheet, and indeed Google Sheets has traditionally had a 256 column limit. Thankfully, with the new version of Google Sheets (now the default), this limit and a number of others no longer exist. Need to generate a huge 100% cloud-based spreadsheet, with up to 2 million cells and the share it securely in real time with others? Not a problem. Google Sheets will do it for you right from within your web browser.

The technical side of how we fixed Ryan's challenge is quite clever, but we won't go into too much detail here. Mainly, we're using Google App Engine to handle all the hard work between the user's Google Spreadsheet and their Xero accounting data. Due to the flexibility of Google App Engine we were able to adapt our code, test it live without impacting existing Blink Reports users, and then make it the production version with zero downtime.

The solution means that Ryan can now generate large reports like the one you see below (click to enlarge). Note the columns stretch all the way to KJ—representing nearly double the original column limit—and it will continue growing as needed.

Xero's built-in reporting engine has good functionality, but when you need to analyze the data in a spreadsheet you're forced to export the report to Excel or Google Sheets format. This leads to static financial data that becomes quickly out of date. To put it bluntly, Xero simply cannot generate reports to rival what we can do on the Google Cloud Platform with Google Sheets.

With Google Spreadsheets and Blink Reports, all of a sudden you can review this month's revenue and expenses by worker, then change two date fields and see a different month (or even year) for comparison.

Offloading the pain of financial reporting to Blink Reports from Interlockit.com means that Ryan at TargetCW can now work more quickly through his finances and focus on the things that matter: generating business instead of generating spreadsheets.

Generating quotes with Google Apps Script and Solve360

Norada's Solve360 CRM is a low cost but very fully-featured cloud-based CRM, and while it integrates deeply with Google Apps, it still needs some custom work every now and then to unlock its full potential. Luckily, we can offer it a helping hand by leveraging the Solve360 API and Google Apps Script.

Rick Klassen of Klassen Specialty Hydraulics contacted us at InterlockIT.com to see if we could streamline the "tedious" quoting system that they had been using: searching for a similar quote, editing it manually to reflect the new requirements, and then saving it to a new folder on a local server, all followed by opening a spreadsheet that doubled as a database and adding the details of the new quote. And forget about trying to create a new quote from a mobile device or when you were out of the office!

We found that with a bit of custom work, we could make Rick's life much easier, and that's exactly what we did. Here's how.

To start, we need to establish the conditions that allow Solve360 and Google Apps to talk to each other--we want to automate repetitive things, so an activity template is used in Solve360. We insert it, and kick off the first of two scripts by clicking "Create new quote":

Insert an activity template to get started.

Now we can use Google Forms to gather the information we need.

The first stage of our quote generator. This particular utility has a number of these pages, with various branches of logic.

Once the user submits the Google Form, the second script is executed. The second script processes the latest submission, reading it from the spreadsheet in which Google Form submissions are saved, and performs a number of actions on it. In a later blog post, we'll detail exactly how some of the scripts work.

Success!

The user receives the above email and in one click can view the completed quote, an excerpt of which is below. It's a live Google Document so the user can continue to make changes to the quote.

Finally, the Solve360 record for the contact is once more updated. This time we can see that the script has added two follow-ups with different dates, created a direct link back to the above Google Document so it can be reached quickly and easily, and inserted an Opportunity record that allows you to track the progress of the deal as it moves forwards.

Here are the final activities that the script adds to the Solve360 record.

As Rick told us, "We have a powerful and flexible solution that not only saves time in the quote creation process, but takes care of the data entry, management, and even reminds our sales people to follow up - all automatically." Creating quotes can now be done from anywhere with an internet connection, and everyone who needs access has it right away.

"These days my colleagues and I can create quotes in a snap (from desktop or mobile), and we each get an email whenever a new quote has been created so we can collaborate on it in real-time, then send it to the customer. This can all happen in a matter of minutes from start to finish."

All this is done in the background, completely transparently to the user, and takes the hard work out of creating quotes, leaving you to focus on the important things—like closing the deal.

Google Apps Script: How to retrieve more than 200 items from a List Page

We're constantly building customer solutions for Google Apps, Solve360, Freshbooks, and Xero with Google Apps Script and Google App Engine.   Below is a handy tip for Google Apps Script developers.

Posted on behalf of Cameron Roberts, Senior Developer, Interlockit.com

When interacting with a Google Sites list page via the Google Apps Script SitesApp service, the documented getListItems() function returns only 200 items, while the maximum length of a List Page in sites is 500 Items. This function takes advantage of undocumented parameter values that can be passed to getListItems() to retrieve all items in batches of 200.  

/*
   Function: getAllListPageItems(page)
  Parameter: page - An instance of the Google Apps Script Services "ListPage" class.
Description: A robust function which uses undocumented Google Services functonality to retrieve ALL items from a list page. At time of writing (June 2012), the Google Services getListItems() method returns only the first 200 items of a ListPage. This function will catch failed API calls and re-try until all list items have been retrieved. This function will also throw an informative exception if the passed parameter is not a valid ListPage object.

Usage Example:
 
   //retrieve items from list page and write to Log.

   var page = SitesApp.getPageByUrl('http://sites.google.com/a/yourdomain.xyz/yoursite/yourListPage');
   var items = getAllListItems(page);

   for(var i in items){
       var item = items[i];
       Logger.log(item);
       //..Perform your processing on list items here.
   }


Written by Cameron Roberts, Interlockit.com

*/

function getAllListPageItems(page){
  if(page && page.getPageType && page.getPageType() == 'ListPage'){
    var items = [];
    var start = 0;
    var size = 200;
    var failed = false;
    do{
      try{
        var item_batch = page.getListItems({start:start,max:size});
        failed = false;
        items = items.concat(item_batch);
        start += size;
      }catch(e){
        failed = true;
      }
    }while(item_batch.length == size || failed);
    return items;
  }else{
    throw 'Parameter passed to getAllListItems was not a List Page: '+page;
  }
}


The relevant post from Google, which exposes the undocumented functionality, is http://code.google.com/p/google-apps-script-issues/issues/detail?id=666

Show your customer addresses on Google Maps

Do a Google Search on mapping customer addresses and you'll get all sorts of overly complicated ways to do it with third party stuff, etc.

Google Spreadsheets does it very easily "out of the box", well that is... in the cloud for free.

Follow these steps published by Google: http://support.google.com/docs/bin/answer.py?hl=en&answer=91601 to use a gadget for mapping addresses from the rows of a Google Spreadsheet.  Last column as tooltip is how you can show the customer name.

In our case we used a Google Apps Script to populate the rows of the Google spreadsheet instantly from our Norada Solve360 CRM using this: http://goo.gl/DyV0F

So now with a few clicks we can display a real time map of our customer locations and refresh it instantly.

If you have a more complex reporting/spreadsheet need that might require Google Apps Script programming contact us at Interlockit.com.

Enjoy!

Google Docs for your iPad vs iCloud

I've led a few iPad training classes for the Toronto/Mississauga branch of a company with 75,000 users on Google Apps.  They've been lots of fun because gadget guys like me that love to figure out cool ways to get things done get paid to show our tricks.

A common question is how do I easily sync presentations, pictures, pdf's, and documents from my desktop to my iPad and back again with iTunes or iCloud?  Well, don't...  It's easier to use Google Docs to store all your files in the cloud so you don't have to remember to sync and fiddle with iTunes

When you open for example a Microsoft Excel spreadsheet on the iPad from your Google Docs the Google servers automatically image it and display it.  At the bottom you can tap on Download to open the file in your iPad's viewer instead.

Not sure how to access your Google Docs from your iPad?  Install the Google Search App and it provides you with convenient icons to access your Docs, Photos, etc.

With more downloads than Angry Birds, Keynote for the iPad is an extremely popular application.  If you need to import Powerpoint presentations into Keynote for offline presenting first upload them to Google Docs from your desktop and make sure you don't convert it to the Google Presentations format.  On your iPad from Safari (not from the Google Search Apps embedded browser) visit http://docs.google.com.  Open the Powerpoint presentation from your Google Docs, scroll to the bottom and tap the Download link.  Now watch the top right for a black bar to appear with a button to open in Keynote.  If you miss it tap just below the standard Safari grey bar.  Click open in Keynote.  Viola, your presentation is now imported into Keynote and ready to go; no need to sync with iTunes or be anywhere near a computer.

Stating the obvious for regular Google Docs users now... but it's even better when you do everything in Google Documents, Google Spreadsheets, and Google Presentations format because team members can all collaborate on the document at the exact same time.  iPads can even edit Google Documents and Google Spreadsheets in real time.

If your team member made last minute revisions to the Google Presentation you don't need to even ask if you have the latest version.  Just open it from Google Docs on your iPad and start your presentation.

Certainly contact us at Interlockit.com if your team could benefit from our iPad training.

We're blushing... Prescient Power shows off a Solve360 report we built in the forums

We've been building custom reports like this with Google Apps Script in Google Spreadsheets for customers since 2010.  This is the first time our work has shown up in the forums.  Thank you Carl for sharing this!

You can view the forum thread at http://norada.com/forums/viewthread/2663/.  Scroll down to Nov. 20th, 2011

"wow - I guess Interlockit have some wizardry in their team!  Nice report - this is exactly what I need."
   --Q

"These custom reports are a really nice touch. Well done to interlockit for demonstrating this capability."
  --davieboy

"Just superb for management reporting and KPI tracking - forcing the “so what?“ question much more than you might expect."
   --Carl