How to Make Pixel Paintings with Google Spreadsheets

You may have been using Google Spreadsheets for budgeting and project management but did you know that the same sheets application can also help you create impressive pixel paintings in minutes? The Google blog recently published a story of two illustrators who created a bright and beautiful wall mural using Google Spreadsheets.

Marina and Mallory connected on Google Hangouts to plan and sketch out ideas, and creatively “hack” Sheets in order to make art: resizing cells into thousands of pixel-like squares, merging cells to create color blocks, creating vibrant color gradients with conditional formatting and cell values, and other cool things we had no idea you could do with Sheets.

The idea is simple. Each cell in the spreadsheet corresponds to a pixel in the painting. You compute the color of the pixel and make it the background color of the corresponding cell. Now resize the spreadsheet cells in small perfect squares and your spreadsheet will look exactly like the original artwork.

How to Paint with Google Spreadsheets

If you would like to create your own spreadsheet art but don’t have the time to carefully paint every cell manually, here’s a simple workaround for you. You can take any photograph, vector art, or any other image and use a Google Script to convert that bitmap image into spreadsheet art.

Watch the video tutorial  or open this Google Sheet for sample artwork.

Create Pixel Art with Google Sheets

It takes few easy steps to make pixel art with Google Sheets. You can use any free image but make sure they are 300 pixels or less for optimal performance.

  1. Open the Google Spreadsheet template and copy it to your own Google Drive.
  2. Go to the Spreadsheet Art menu, choose the Image Upload option and select the picture that you’ve downloaded in the previous step.
  3. The sheet will now parse every single pixel of your image and write the corresponding hex color codes in the spreadsheets cells.
  4. Select the “Apply Colors” option and the Google Script will set the background color of every spreadsheet cell equal to the cell value.
  5. The cells in the spreadsheet are rectangles whereas pixels are perfect squares. Select step 3 to resize every cell in the spreadsheet as a square.

And that’s it. Your spreadsheet art is now ready.

The end result may appear slightly pixelated (video) because we have used a small image as the source template but impressive nonetheless. You can download the Google Sheet as a PDF file or save it in Microsoft Excel format.

google-spreadsheet-art.jpg

Pixel Paintings made with Google Spreadsheets – Link


The story, How to Make Pixel Paintings with Google Spreadsheets, was originally published at Digital Inspiration by Amit Agarwal on 08/08/2016 under Google Docs, Software.

How to Embed a Muted YouTube Video Player in your Website

It is easy to embed YouTube videos in your website. You grab the default IFRAME embed code, paste it anywhere inside your web page and you’re done. YouTube offers basic customization – you can modify the player dimensions or hide the YouTube branding – but if you would like to exercise more control over the  behavior of the embedded player, YouTube Player API is the way to go.

This tutorial explains how you can embed a YouTube video that will automatically play when the web page is loaded but with muted audio.

For instance, a products website may use short screencasts to highlight features and these videos will autoplay when the page is loaded. The volume is however set to 0 and the user can manually click to un-mute the video. Similarly, if you are using YouTube video backgrounds, it makes more sense to embed muted videos that run in a loop.

Mute the Embedded YouTube Player

See the demo page to get an idea of what we are trying to do here. The page loads, the video plays but with the audio slide is all the way down.

This is easy. Go the YouTube video page and note down the ID of the video from the URL. For instance, if the YouTube video link is http://youtube.com/watch?v=xyz-123, the video id is xyz-123. Once you have the ID, all you have to do is replace YOUR_VIDEO_ID in the following code with that string.

<div id="muteYouTubeVideoPlayer"></div>

<script async src="https://www.youtube.com/iframe_api"></script>
<script>
 function onYouTubeIframeAPIReady() {
  var player;
  player = new YT.Player('muteYouTubeVideoPlayer', {
    videoId: 'YOUR_VIDEO_ID', // YouTube Video ID
    width: 560,               // Player width (in px)
    height: 316,              // Player height (in px)
    playerVars: {
      autoplay: 1,        // Auto-play the video on load
      controls: 1,        // Show pause/play buttons in player
      showinfo: 0,        // Hide the video title
      modestbranding: 1,  // Hide the Youtube Logo
      loop: 1,            // Run the video in a loop
      fs: 0,              // Hide the full screen button
      cc_load_policty: 0, // Hide closed captions
      iv_load_policy: 3,  // Hide the Video Annotations
      autohide: 0         // Hide video controls when playing
    },
    events: {
      onReady: function(e) {
        e.target.mute();
      }
    }
  });
 }

 // Written by @labnol 
</script>

Next place the edited code in your web page and the embedded video would automatically play but muted.

You can further customize the player by modifying the various player variables as commented in the code. For instance, if you set loop as 1, the video will play in a loop. Set fs to 1 to show the fullscreen button inside the video player. Internally, the player is embedded using the YouTube IFRAME API. When the page is loaded, the onReady event runs that mutes the video.

youtube-mute-video-embed.png

The embedded YouTube video will autoplay, but muted.


The story, How to Embed a Muted YouTube Video Player in your Website, was originally published at Digital Inspiration by Amit Agarwal on 04/06/2016 under Embed, YouTube, Internet.

Make Stunning Video Presentations with Spark Video from Adobe

Adobe Voice has long been my favorite digital storytelling app for making video presentations and photo slideshows on iOS device. All you have to do import your photos, type some text, add your own voice narration and a stunning video is ready for uploading on to YouTube or Facebook.

Here’s a video story that our 10-year old made with Adobe Voice. The background music, transitions and other effects are automatically applied by the Voice app to make your video looks both cinematic and professional.

I have all good things to say about Adobe Voice except that you need an iPad or iPhone to create videos. Well, until now as Adobe has quietly launched a new suite of web apps that, among other things, will let you use Adobe Voice inside your desktop browser. The suite, known as Adobe Spark, includes tools for creating video stories, magazine-style web pages and typography posters (think of Typorama but for the web).

And the price is just right. $0.

To get started, go to spark.adobe.com and sign-in with your Facebook or Google Account. This is mandatory because all your work will be auto-saved under this account and will also be accessible on your iPad and iPhone.

Spark Video offers a PowerPoint style layout. You have a plethora of cinematic themes to choose from and each theme has its own set of background music, transitions and fonts. Changing a theme for your video is as simple as choosing one from the sidebar.

Video Themes

Your slides can have photos and text or both. If you click that little ‘speech’ icon, you can alter the position of the photo on the slide or mark the main point that should be focussed during transitions.

You can either import photos from the computer or there’s a built-in search engine to help you search photos that are in the Creative Commons domain. You can also import photos from Dropbox or Google Photos. Spark Video automatically add the photo sources in the closing-credits of the movie.

Search Photos

Your movies stay forever on the Adobe Spark website and you get a permanent link to share that video with friends. Alternatively, you can download the raw video in MP4 format for uploading to other sites like YouTube or Facebook or publish as an iTunes Podcast.

Adobe claims no copyright over the video or embedded music so you can legally download the video and do anything. For instance, some people may want to remove the Adobe Voice branding that’s added in the last slide and you can easily do with any video editing software.

Export Video

Adobe Spark will make it easy for anyone to make cinematic quality presentations will little effort. The one feature that you’ll miss though is that there’s no option to import GIFs and video clips into your slides.


The story, Make Stunning Video Presentations with Spark Video from Adobe, was originally published at Digital Inspiration by Amit Agarwal on 19/05/2016 under Presentations, Tools, Internet.

How to Copy an Entire Folder to Another Folder in Google Drive

Google Drive, launched almost 4 years ago, is an online vault where you can store all your files and documents. It offers nearly all the functionality that you would expect in a file manager app making it easier for you to manage files from within the browser. You can move files between folders, rename files, differentiate them with colors, add text notes for easier lookup and more.

However the one piece that is still missing in Google Drive is the ability to copy folders. You can duplicate any file in Google Drive by right-clicking the file and selecting “Make a Copy” but this option is not available for folders. A Google Script exists to copy folders in Google Drive but it is complicated.

The option to duplicate folders doesn't exist in Google Drive

The option to duplicate folders doesn’t exist in Google Drive

How to Copy Folders in Google Drive

Wouldn’t it be nice if there were an xcopy (Windows) or rsync (Mac, Unix) like command for Google Drive that would recursively copy everything inside a folder, easily It should not only copy all the files and subdirectories but also retain the directory tree structure and maintain the shared file permissions.

Well, we are not sure if Google will ever provide an option to duplicate folders in Google Drive but there’s an open source web app by Eric YD that does exactly what you are looking for. Here’s how to get started:

  1. Go to labnol.org/xcopy and click the Authorize button.
  2. Allow the app to access your Google Drive files. The app would perform the copy operation directly inside your Google account without involving a third-party server.
  3. Once authorized, select the source folder using the Google File picker and specify the destination folder name.
  4. Click the Copy Folder button to begin the cloning process.

That’s it. The script will run in the background and copy files, one-by-one, into the target folder.  It will log everything in a Google Spreadsheet so you know what’s happening behind the scene.

While optional, the app can also retain permissions and in the case the copied files will be editable/viewable by the same people as the original files. The copying process would however take longer if you choose to copy the sharing permissions.

Also see: Add Files in Google Drive to Multiple Folders

Copy Folders to another Google Drive Account

Say you have a folder in one Google account (A) that you would like to copy to another Google account (B). While the app doesn’t support copying across accounts, there’s a simple workaround:

  1. Use labnol.org/xcopy to copy the folder in its original account (A).
  2. Log into account B, create a blank folder and share it with account A.
  3. Log into account A and move the copied folder into the shared folder.
  4. Go back to account B and remove the sharing permissions for the folder.

The Alternative – If you are not comfortable giving access to your Google Drive to another app, you can manually copy folders using the Google Drive desktop client for Windows and Mac. Go to Windows Explorer (or Finder), select the source folder and press Ctrl+C followed by Ctrl+V to duplicate the folder.

This would however not retain the original file permissions. The other downside is that Google Drive will have to reupload all the copied files whereas in the previous case, the copying happened on Google servers directly.


The story, How to Copy an Entire Folder to Another Folder in Google Drive, was originally published at Digital Inspiration by Amit Agarwal on 03/05/2016 under Google Drive, Internet.

How to Copy an Entire Folder to Another Folder in Google Drive

Google Drive, launched almost 4 years ago, is an online vault where you can store all your files and documents. It offers nearly all the functionality that you would expect in a file manager app making it easier for you to manage files from within the browser. You can move files between folders, rename files, differentiate them with colors, add text notes for easier lookup and more.

However the one piece that is still missing in Google Drive is the ability to copy folders. You can duplicate any file in Google Drive by right-clicking the file and selecting “Make a Copy” but this option is not available for folders. A Google Script exists to copy folders in Google Drive but it is complicated.

The option to duplicate folders doesn't exist in Google Drive

The option to duplicate folders doesn’t exist in Google Drive

How to Copy Folders in Google Drive

Wouldn’t it be nice if there were an xcopy (Windows) or rsync (Mac, Unix) like command for Google Drive that would recursively copy everything inside a folder, easily It should not only copy all the files and subdirectories but also retain the directory tree structure and maintain the shared file permissions.

Well, we are not sure if Google will ever provide an option to duplicate folders in Google Drive but there’s an open source web app by Eric YD that does exactly what you are looking for. Here’s how to get started:

  1. Go to labnol.org/xcopy and click the Authorize button.
  2. Allow the app to access your Google Drive files. The app would perform the copy operation directly inside your Google account without involving a third-party server.
  3. Once authorized, select the source folder using the Google File picker and specify the destination folder name.
  4. Click the Copy Folder button to begin the cloning process.

That’s it. The script will run in the background and copy files, one-by-one, into the target folder.  It will log everything in a Google Spreadsheet so you know what’s happening behind the scene.

While optional, the app can also retain permissions and in the case the copied files will be editable/viewable by the same people as the original files. The copying process would however take longer if you choose to copy the sharing permissions.

Also see: Add Files in Google Drive to Multiple Folders

Copy Folders to another Google Drive Account

Say you have a folder in one Google account (A) that you would like to copy to another Google account (B). While the app doesn’t support copying across accounts, there’s a simple workaround:

  1. Use labnol.org/xcopy to copy the folder in its original account (A).
  2. Log into account B, create a blank folder and share it with account A.
  3. Log into account A and move the copied folder into the shared folder.
  4. Go back to account B and remove the sharing permissions for the folder.

The Alternative – If you are not comfortable giving access to your Google Drive to another app, you can manually copy folders using the Google Drive desktop client for Windows and Mac. Go to Windows Explorer (or Finder), select the source folder and press Ctrl+C followed by Ctrl+V to duplicate the folder.

This would however not retain the original file permissions. The other downside is that Google Drive will have to reupload all the copied files whereas in the previous case, the copying happened on Google servers directly.


The story, How to Copy an Entire Folder to Another Folder in Google Drive, was originally published at Digital Inspiration by Amit Agarwal on 03/05/2016 under Google Drive, Internet.

How to Embed Facebook Message Buttons in your Website

Email has always been the preferred mode for reaching out to people on the Internet. Websites would put their email addresses on web pages and made them clickable hyperlinks using the mailto protocol. Things have changed and while email is still considered important, the new generation is known to prefer instant texting apps like Facebook Messenger over email. For them, writing an email is as boring as creating a résumé.

This step-by-step guide explains the various techniques that you can use to easily embed the Facebook Messenger button in your website. When someone clicks the Message Me button, it will directly launch the Facebook Messenger app on their mobile phone or the messenger.com website if they are on the desktop. You can choose to receive messages in your Facebook profile or your Facebook page, if you have one.

A: Simple Facebook Messenger Link

The default Facebook Messenger button is rendered using JavaScript but you can also write them in pure HTML without requiring JavaScript. The advantage is that these buttons (see demo) would show up even if the user has enabled ad blockers and second, you can embed message links inside email newsletters.

Facebook Messenger

All you have to do is replace “XYZ” in the snippet below with your Facebook vanity username. If you don’t have a username yet, you can use your numerical Facebook profile ID instead. The link may be customized with CSS to resemble a button as seen in this live demo.

 <a href="https://m.me/XYZ">
    Message us on Facebook
 </a>

Tip: If you are not seeing messages sent to your Facebook profile, it is because Facebook automatically hides messages from people that are not in your contacts / friends list. Go to facebook.com/messages, click the “More” dropdown and choose “Filtered” to see all messages that are not in your main inbox.

B: Facebook Messenger Button

This is the default Facebook Messenger button (see live demo) rendered with JavaScript. Unlike the previous option that allows messaging to personal profile as well, this button is only available to Facebook Page owners for them to receive messages from other Facebook users.

To get started, copy-paste the following snippet in your blog template but remember to replace “XYZ” with the numeric ID of you Facebook Page. You can set the color as white for a blue text on white background button.

<script>      
      window.fbAsyncInit = function() {
        FB.init({
          appId      : '95100348886',
          xfbml      : true,
          version    : 'v2.6'
        });
      };

      (function(d, s, id){
        var js, fjs = d.getElementsByTagName(s)[0];
        if (d.getElementById(id)) {return;}
        js = d.createElement(s); js.id = id;
        js.src = "//connect.facebook.net/en_US/sdk.js";
        fjs.parentNode.insertBefore(js, fjs);
      }(document, 'script', 'facebook-jssdk'));      
    </script>

    <div class="fb-messengermessageus" 
         messenger_app_id="95100348886" 
         page_id="XYZ" 
         color="blue"
         size="large">
    </div>

Tip: If you are unable to use the Facebook Messenger button, it is likely that your Facebook Page is configured to not receive messages. Go to your Facebook Page settings, choose General, Messages and turn on the option that says “Allow people to contact my Page privately by showing the Message button”.

C: Facebook Messenger Box

You have seen the Facebook Like box embedded in websites but did you know that the same box can also include an inline form allowing any Facebook user to contact you without even leaving your webpage. Here’s a live demo.

In this case you need to replace “XYZ” in the snippet below with the vanity username or ID of your Facebook Page. Anyone who is logged into Facebook can message you from your website itself, similar to regular contact us forms.

<script>
      window.fbAsyncInit = function() {
        FB.init({
          appId      : '95100348886',
          xfbml      : true,
          version    : 'v2.6'
        });
      };

      (function(d, s, id){
        var js, fjs = d.getElementsByTagName(s)[0];
        if (d.getElementById(id)) {return;}
        js = d.createElement(s); js.id = id;
        js.src = "//connect.facebook.net/en_US/sdk.js";
        fjs.parentNode.insertBefore(js, fjs);
      }(document, 'script', 'facebook-jssdk'));
    </script>

    <div class="fb-page" 
         data-href="https://www.facebook.com/XZY/" 
         data-tabs="messages" 
         data-width="400" 
         data-height="300" 
         data-small-header="true">
      <div class="fb-xfbml-parse-ignore">
        <blockquote></blockquote>
      </div>
    </div>

You should also check the Facebook Page Plugin to learn about all the other configuration options that can be easily set using the data attributes.

Related: How to Embed Facebook Posts in Webpages


The story, How to Embed Facebook Message Buttons in your Website, was originally published at Digital Inspiration by Amit Agarwal on 03/05/2016 under Code, Embed, Facebook, Internet.

How to Delete the Useless Photos in your WhatsApp Automatically

WhatsApp is probably the largest space hog on our mobile phones. We all have friends and relative who diligently forward us every single motivation quote and meme they’ve received from their own network. Some think it is their duty to wake us with a “good morning” message accompanied with photos of the rising sun or chirping birds.

The bigger problem with WhatsApp is that these ‘spam’ messages often originate from contacts who are close to you in real life and thus blocking them would be considered rude. You have an option to mute WhatsApps groups but the downloaded images would still take up precious space on your mobile phone.

A simple solution to the problem would be that you open the Photo Gallery app on your Android phone, or use a File Manager app to locate the media folder of WhatsApp, and delete the entire folder containing those WhatsApps images. But since WhatsApp makes no distinction between real photographs and useless forwards, you risk deleting the good pictures too.

Are junk WhatsApp images filling up your phone's memory?

Are junk WhatsApp images filling up your phone’s memory?

Siftr, an Indian startup founded by ex-Adobe employees, has launched an intelligent Android App that can help you get rid of all the junk photos from your WhatsApp with no effort.

The app, Magic Cleaner, scans the media folder of your WhatsApp app and automatically detects all the junk images including screenshots, memes, video screen grabs, cartoons and other pictures with overlay text. You are then offered an option to delete all the detected images in one go.

Previous: Create Photography Websites with Siftr

How does it work? Like Cloud Vision, Google’s image recognition API, Siftr has developed their own image recognition engine that analysis the content of an image to determine whether it’s junk or not. It requires an active Internet connection because the image analysis is done on Siftr servers and not locally on the mobile phone. However it is unlikely to blow up your data plan as the app only uploads a small hash of the image and matches it against their own database of images.

I ran the photo cleaner app against a WhatsApp account that had about 4000+ images and it could successfully clean it up in about 10 minutes. The accuracy was very impressive. The app is free but you can only auto-delete a limited number of images in a single run. If you would like to delete more images, you either need to invite a friend to use the app or wait for a day.

WhatsApp Photo Cleaner

If you have WhatsApp, which you probably do, Magic Cleaner is a must-have app. An iPhone version is in the works. Also, though the WhatsApps images are deleted from the memory card, a blurred thumbnail will still exist inside your WhatsApp message logs and you’ll have to erase them manually.

Also see: Sync WhatsApp Photos with Google Drive or Dropbox


The story, How to Delete the Useless Photos in your WhatsApp Automatically, was originally published at Digital Inspiration by Amit Agarwal on 02/05/2016 under Android, WhatsApp, Internet.

Learning CSS with Cats and a Dinner Table

CSS, short for Cascading Style Sheets, are rules (or “styles”) that define how content should look on a web page. To give you an example, if your web page contains a table, you can use CSS to specify the thickness of the border and the color of text inside that table.

Learning CSS is easy. There are a plethora of good online tutorials or, if you prefer the traditional route, get a book. My favorites CSS books include HTML & CSS by John Duckett and CSS Secrets by Lea Verou.

You should also watch the YouTube series by Travis Neilson and Guy Routledge, probably the best free video resources for learning everything about CSS.

I recently stumbled upon a couple of web resources that take the help of cats and post-it notes and make learning CSS even more interesting and entertaining.

CSS Basics with Post-It Notes (link)

Designer Kaylan takes the help of colorful post-it notes for visualizing the the basic concepts of CSS.

CSS Box Model

CSS Selectors Explained with CSS (link)

If you have figured out how to write selectors in CSS, half the battle is one. Mike Borsare uses #cats to help you learn the basic CSS selector syntax.

CSS Selectors

CSS Dinner Table (link)

If you know the basic of CSS selectors, use the Dinner table to practice your CSS skills. Here you have plates and fruits placed on a dinner table and your task is to select the various elements using selectors.

CSS Dinner Plate

Related: Learn to Code Online


The story, Learning CSS with Cats and a Dinner Table, was originally published at Digital Inspiration by Amit Agarwal on 15/04/2016 under Code, Internet.

How to Create Multiple Copies of an Email Draft inside Gmail

You have composed a draft inside Gmail and would like to send the same email message to multiple people, separately. The subject, the email body, the attachments remain unchanged and the only thing that differs in each message is the recipient’s email address. Maybe you are sending your résumé to multiple companies. How can you do this without having to copy-paste the subject and body of the draft message multiple times?

When you compose a draft email in Gmail and hit the Send button, the email is automatically removed from the Drafts folder and moved to the Sent Items folder of Gmail. Unlike Microsoft Outlook, Gmail doesn’t offer an option to create multiple copies of an existing email message.

Gmail Draft Emails Copy

Create Duplicate Email Messages in Gmail

There are two easy ways to create duplicate draft emails inside Gmail. You can either use Mail Merge for Gmail or, if you are looking for a more simple one-click option, use my new Duplicate Gmail web app written with Google Scripts.

Here’s how to get started.

  1. Open your Gmail mailbox, compose a new email message and save it as a draft.
  2. Go to labnol.org/duplicate and authorize the app to access your Gmail account. This is necessary since the app will read your Gmail drafts and create copies inside your Gmail account.

The Gmail clone tool works on both desktop and mobile phone so you can copy email messages on a mobile phone as well.

To quickly clone a message to send separately to someone else, select the source draft from the drop-down, then select the number of copies you wish to have and click the Create Drafts button. The copied drafts will include the complete contents of the original message, including file attachments and inline images.

If you would like to create more copies of the draft, reload the page and repeat the steps. Internally, the app uses the Gmail API to create drafts.


The story, How to Create Multiple Copies of an Email Draft inside Gmail, was originally published at Digital Inspiration by Amit Agarwal on 15/04/2016 under GMail, Internet.

How to Track Google Spreadsheet Views with Google Analytics

You have been using Google Analytics to track visitors (or page views) on your website but did you know that the same analytics service can also be used for tracking views inside Google Spreadsheets. You insert a little snippet of tracking code inside your spreadsheet and when someone opens the sheet, that visit will be recorded permanently in your Google Analytics account.

track-google-spreadsheets.png

Google Analytics provides a JavaScript snippet that can be inserted into web templates for tracking visits. You cannot insert JavaScript inside the cells of a Google Spreadsheet but we can use the IMAGE function combined with some Apps Script to enable tracking inside spreadsheet. The visit is recorded as an “event” and not a “page view” and thus your spreadsheet opens will not artificially inflate your Google Analytics reports.

To get started, go to your Google Analytics dashboard and make a note of the Google Analytics tracking ID which is a string like UA-12345-67. This web tutorial explains how you can locate the ID inside your Analytics dashboard.

Now open any Google Spreadsheet that you wish to track and go to Tools, Script Editor and copy-paste the following code. This is a custom Google Spreadsheet function that will embed the 1×1 tracking GIF image in our spreadsheets.

/**
 * Track Spreadsheet views with Google Analytics
 *
 * @param {string} gaaccount Google Analytics Account like UA-1234-56.
 * @param {string} spreadsheet Name of the Google Spreadsheet.
 * @param {string} sheetname Name of individual Google Sheet.
 * @return The 1x1 tracking GIF image
 * @customfunction
 */

function GOOGLEANALYTICS(gaaccount, spreadsheet, sheetname) {
  
  /** 
  * Written by Amit Agarwal 
  * Web: www.ctrlq.org 
  * Email: amit@labnol.org 
  */
  
  var imageURL = [
    "https://ssl.google-analytics.com/collect?v=1&t=event",
    "&tid=" + gaaccount,
    "&cid=" + Utilities.getUuid(),
    "&z="   + Math.round(Date.now() / 1000).toString(),
    "&ec="  + encodeURIComponent("Google Spreadsheets"),
    "&ea="  + encodeURIComponent(spreadsheet || "Spreadsheet"),
    "&el="  + encodeURIComponent(sheetname || "Sheet")
  ].join("");
  
  return imageURL;

}

Save the code, close the Apps Script editor window and return to the spreadsheet.

Click an empty cell and insert the following formula. The cell will be blank but it contains an embedded image. You may want to change the background color so it is easy to figure out which cell in the spreadsheet contains the tracking formula.

Google Formula

The GOOGLEANALYTICS() formula takes 3 parameters – the analytics ID, the spreadsheet name and the sheet name. This helps if you would like to separately track individual sheets inside a spreadsheet.

Now open the spreadsheet in a new browser window and go to Google Analytics, Real Time, Overview to test if the tracking is working. It may sometimes take a minute to record the visit. If you would like to see all the visits, go to Behavior – Events – Overview and click on the Google Spreadsheets category.

google-analytics-report.png

Also see: Track Gmail Messages with Google Analytics

The tracking would work even if the user has enabled ad blocking and that’s because Google Spreadsheets, like Gmail, serves images through a proxy server. The downside is that you will never know the location of the visitor since all visits will be show up as United States (the location of Google servers).


The story, How to Track Google Spreadsheet Views with Google Analytics, was originally published at Digital Inspiration by Amit Agarwal on 09/03/2016 under Google Analytics, Google Docs, Internet.