ArrayFormula, Match and Offset in Google Spreadsheets

This is a repost of our contribution to the Google Gooru website that provides Tips, Tricks, and Tools for Gmail & Google Apps.

The arrayformula in Google Spreadsheets has a number of great different use cases. This video pairs the arrayformula with Match and Offset to pull in a constantly updating range of data in one sheet to create a chart in another sheet.

The advantage of using the arrayformula in conjunction with Match and Offset is it allows you to constantly add rows to your selected range, so you never have to worry about updating the formula. This would work great, for example, if you were pulling from the responses from a Google Form and wanted to consolidate them into a chart.

This video also happens to use our recently released Blink Reports Add-on for Xero Accounting!

Embed an image in your Google Apps signature using Google Drive

A recent update to Google Drive has broken this method of inserting images. To see updated instructions, click here.

If you're used to Outlook or other traditional mail clients, Google Apps' signature can be a little confounding at first blush. Inserting an image looks like it should be as easy as clicking a button and choosing the image you'd like. However, if you click the insert image button, what you'll see instead is this:

Now you're stuck unless you use third-party image hosters like imgur, ImageShack, or Photobucket. While these products work just fine for general hosting, they might be blocked by corporate firewalls, or cause a host of other problems. But are you really stuck?

As is usually the case when Google goes against the grain, there's a reason why they've chosen this route rather than the "traditional" route of attaching an image file to each email you send. The first is the obvious one--it saves you from actually having a file attachment on each message you send, which reduces e-mail message sizes. Secondly, and more importantly, it helps you avoid having your messages sent to spam. Many mail servers look for images attached to messages as a sort of "flag" that the message might be spam.

Luckily, if you use Google Apps for Business (or, indeed, a free Gmail account), you have the Google Drive cloud storage application to help you out. First, find the image you'd like to use in your signature and upload it to your Drive. Next, ensure that the image is shared publicly (with view-only rights, not edit rights), so that anyone you send emails to will be able to see the image without having to be signed into a Google Account.

Once you click "Save", you should be taken back to the initial sharing dialogue box, which will show you who the file is currently shared with along with the URL to access it in a text box along the top. What we need here is the File ID, which is the two-part string of characters in between /d/ and /edit, as seen below:

Now, we need to trick Google Drive into thinking we're exporting the image, so that we can access it outside of the standard Google Drive image viewer. To do so, we'll use this URL: http://drive.google.com/uc?export=view&id=<FileID>. Here, we replace <FileID> with the text highlighted in red above. So we'll use http://drive.google.com/uc?export=view&id=0B_T9jgOMjQ5lN2Z4UWhpZGRTM1E. To test if you've got the right image, simply copy and paste your URL into your browser's address bar and hit enter. If you see only the image show up, then you've done it correctly, and can use this URL to insert an image into your signature.

Go back to your signature editor, click the Insert Image button, and paste in the URL we generated a moment ago. If you see the image load in a preview just below the URL box (it might take a few seconds if it's a large image), then you've got the right URL. Click OK, and you're done! That image will now be linked in your signature, won't take up extra space, and shouldn't trigger spam filters.

All this work saves you from having an attachment on every message you send, which certain spam filters don't like, and will allow you to quickly and easily change your signature's image, as long as you use the "export" URL listed above.

Using YouTube Live and Hangouts On Air for Google Apps domain-restricted town hall meetings

If you've ever wanted to hold a private town hall meeting for your organization but haven't been able to get everyone involved because of geography, time zones, and other factors, you can now join the ranks of Conan O'Brien, Tyra Banks, The New York Times, Deepak Chopra, Cadbury, and the Miami Hurricanes by using Google+ Hangouts On Air and YouTube Live.

Best of all it's included with your Google Apps for Business subscription for no additional cost.

There are a couple of prerequisites to being able to broadcast live either to the Internet at large or just to your organization. First, you must have a YouTube account that is in good standing--meaning you've got no strikes on it. If you don't have a channel, create one and verify it via SMS. Once your YouTube account is configured, make sure that your Google+ profile or business page is connected to your YouTube channel. If you've just created a YouTube channel, this is done automatically for you. For existing channels, you can connect it to a Google+ profile or a Google+ page. If you previously used your Google+ page to broadcast to a Google+ profile administrator’s channel, you need to link the page to its own YouTube channel.

Now you're ready to broadcast using Google+ Hangouts On Air and YouTube Live! Navigate to your Video Manager:

Once you're in your Video Manager, click Live Events in the left-hand navigation menu:

You'll notice at first that there are no events scheduled. Click "Schedule a new event" or the "New live event" button on this page and you'll be taken to a page with lots of options. Most of them are pretty self-explanatory, such as title and time, however there are a few things that need to be configured to make sure you're not broadcasting to everyone in the world.

On the "Basic info" tab, click the "Public" drop-down box and select "Private". A new text entry box will be added to the page and you'll be able to share the broadcast with Google+ Circles, specific people, or your domain. For a town hall-style meeting, we recommend setting it to your Google Apps domain name circle, though you can add as many or as few people as you'd like. When you pick the Private option, anyone who wishes to view the broadcast must have a YouTube or Google+ account tied to the e-mail address or domain you've shared the broadcast with and must be signed into that account. Some of your Google Apps users might have to create their Google+ profile for the first time to access the YouTube stream.

Make sure "Type" is set to "Quick" so that you don't have to do any extra setup work, and leave the Advanced Settings screen alone, unless you'd like to add location data or a recording date.

Click the blue "Go live now" button, and you'll receive one final warning to let you know that you're about to go live.

If everything is okay, click the OK button, and now you're broadcasting! You can monitor the feeds on the fly through the Google+ Hangouts On Air window that pops up, or by clicking Live Control Room on the broadcast setup page:

From this control room, you can monitor people who are watching your video, add or remove abusive viewers, mute attendees, and more. It's like having your own TV station control room, right on your PC or Mac!

And, of course, anyone who tries to view your broadcast without the proper rights to see it will simply see a message pop up telling them that the video is private.

Once your event is done, YouTube will automatically save the video online with its sharing permissions intact, so that anyone who missed it live can watch it again whenever they want.

Google+: not a social network but a social layer that's mission critical to your marketing efforts

Google+ has over half a billion monthly active users, over 1 billion registered users, and the +1 button gets clicked 5 billion times per day. Some forecasts suggest that it is on track to overtake Facebook in 2016. The Google+ audience tends to be made up of early adopters, technophiles, people in various high-tech industries, and more enthusiastic users of technology.

Yet the majority of small- and medium-sized businesses continue to ignore it because, in their eyes, everyone is using the currently more popular Facebook. We see it in our own Google Apps customer base, and maybe your company behaves the same way.

At Interlockit.com we've chosen to largely ignore non-Google services in our own marketing efforts and we still show on the first page of organic search results for "Google Apps Toronto". Maybe we could achieve first place if we worked hard on a Twitter and Facebook presence, but we feel that with limited time and budget, focusing on Blogger and Google+ with their integrated "social layer" is generating the highest return on investment.

97% of consumers search for local businesses online. Your local Google+ page connects you with customers in Search, Maps, and Google+ no matter what device they are using.

Note this article's headline: "not a social network but a social layer." Google+ is a social layer intimately connected to your business' search engine results. The "social layer" shows who in your circles has +1'd your search engine results. You're certainly more likely to click on the link that your contacts have recommended.

Google+ is integrated with paid advertisements on Google's Adwords advertising platform. Search for Cadbury on Google.com to see how their ads integrate with their 3.1 million followers on Google+.

Google+ is a key connector, the glue that integrates your social and business network across Google's vast offering of services.

If your business still doesn't have a Google+ Business page, set one up now and link it to your website!