Browse Your Google Drive Files in Column View

Sunday, August 28, 2016, Hari

Today, cloud has taken over then entire world. We love to store data in cloud rather than in personal computer. Data in cloud is easily accessible everywhere. Google Drive is my favorite cloud storage platform, which has gained a lot of popularity in recent years. It offers a pretty good free quota to start with. But today's topic is not about storage, rather we will see some ways to view and access our files in Google Drive.

View your files in columns

We have created a script which can help you to view your Google Drive files in columnar layout. This is useful to quickly view deeply nested files and to switch to another folder.

Are you a developer ? Visit the GitHub link to find details about code and deployment instructions.

Google Drive bonus tips

  • How to count number of files in a Google Drive folder
    Google Drive does not display the number of files in a folder, but there is definitely a workaround. Just select the files (In Windows it is Ctrl+A), click and drag slightly. Now it will start showing you number of files at your cursor.

  • View list of available shortcut keys
    Simply press "?" (shift+/) and you can see all shortcut key available. I never realized this exists. Really cool. There is a shortcut to see the last toast message that has appeared on your screen. Press m and check what you observe. If you do not see any message it means you have not performed any action. Do some actions like star a file or rename it, then press m to see the message. Helpful when you forgot the last action you did or accidentally did some changes.

  • Change density
    Double pressing q will allow you to change the density of data on your screen. Impressive!

 

Automatic Web Page Screenshot Capture With Google Apps Script

Friday, August 26, 2016, Hari

This app can help you to take regular screenshots of your webpage automatically. It has been created with Google Apps Script  and uses trigger to automate the process. Using this application you can track how a webpage was looking like 3 years before. You don't need to do this manually. Simply click on "Start capturing" and it will capture the screenshots and store in your Google Drive.

Webpage Screenshot Capture app V1.0

How it works

This app uses the API provided by https://screenshotlayer.com. It sends your webpage url to the API with certain parameters. The response is received as an image. Google Apps Script receives the response and stores that as a new image in your Google Drive. Its main attractive feature is it accepts full page screen capturing.

How to start capturing your webpage

  • Go to https://screenshotlayer.com and create an account. After sign up you will get an API key, keep it with you. This API key will be provided in the spreadsheet.
  • Now open the app and go as per the instructions provided.
  • Put the required parameters.
  • After providing details simply press the Verify now button to verify whether the API works properly or not.
  • If the API works, it will create a folder in your Google Drive and put the screenshot inside it.
  • Once you make sure that the API works well, you can press the Start capturing button to automate the process in a regular interval.
  • The frequency of capturing you can customize from the in-cell-dropdown.

Hope this helps you to keep track of your website's appearance and layout. If you have any question then drop a comment below.

Share Your Google Contact Groups With Others

Friday, August 12, 2016, Geetanjali

Do you want to share some of your Gmail contacts or in fact a contact groups with others ? You want somebody to edit and update your contact groups ? How do you do that ? As there is no default option available in Google Contacts, we have created a script which can help you to share your Google Contact Groups with some persons. You can give him edit/view access and can also revoke his access anytime.

Features of this script

  • Create user
    You can create a new user by providing a username and password
  • Grant Permission
    You can give edit/view access for a contact group to the user(s)
  • Web App
    Provide the Web App URL to your users to access the shared contact groups
  • Revoke Permission
    At any time you can revoke the permission from the user either by changing the credentials or deleting the entire user from the list. After the permission is revoked, users can not access the contact group or its contacts

How the admin setting looks like

Your users will see following page, where they can view or edit contact groups.

  • User Login
    As an admin you have to provide the Web App URL to the user. User can open that URL and login with their credentials (username and password that you have set for him)
  • Accessing Contact Groups
    After login user can see the list of contact groups which he has permission to view. He can also view the list of contacts and contact details of each group
  • Updating Contacts
    If the user has edit access for a contact, a save button will be visible to him. He can add new fields in phones, emails, addresses, URLs & companies and can save the details

Purchase and Download

You can purchase Contact Groups Manager from the below link. Its cost is only $8.99.

Download here

After you get the app, go to File → Make a copy... Then you can start using it.

For any query drop a comment at support@tabgraf.com

How to Convert Google Spreadsheet to PDF and Send as Email Attachment

Saturday, August 6, 2016, Hari

Now a days Google Drive has gain a vast popularity. We have seen people gradually migrating from desktop based office software to browser based Google Sheet and Docs. Sometimes we need Google Sheet data to be emailed to somebody as PDF and at times we also want this to happen recurrently. Keeping these in mind we have come up with an interesting solution.

Spreadsheet Mailer - Google Spreadsheet Add-on

This spreadshheet add-on can help you to easily convert an active spreadsheet to PDF and email as an attachment. 

Features

  1. Convert spreadsheet to PDF - just you need to install the add-on
  2. Select which sheets to export
  3. If exporting as PDF, provide other configuration parameters such as title, grid, layout etc.
  4. Schedule recurring emails with specific time interval such as hourly, daily, weekly etc.
  5. View all scheduled emails and cancel them anytime.

How it works ?

To convert Google Spreadsheet to PDF or any other file we can append specific query parameters to its URL. Here is an example URL which shows all params to get the output in different format.

https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key=SPREADSHEET_ID&exportFormat=pdf&gid=SHEET_ID&gridlines=true&portrait=false&fzr=true&sheetnames=false&fitw=true

Parameter Meaning
exportFormat It can be pdf, xlsx or csv. It downloads the spreadsheet in specific file format.
gid Download a specific sheet instead of the entire spreadsheet. Get the gid from the spreadsheet URL.
gridlines Show or hide gridlines.
portrait Set the layout. Portrait or landscape.
fzr Freeze the first row in each page.
sheetnames Show or hide sheetnames in page header.
fitw Display all columns in a single page, font-size may become smaller.

Bonus tips

  • Just append your spreadsheet ID, grid ID in above URL and hit in browser to check how it works.
  • To export all sheets just ignore the gid parameter.
  • When exportFormat is other than pdf, other params such as gridlines, fzr etc. will be ignored. 
  • Hidden sheets are not exported with above URL and this is expected.
  • CSV format only contains data but no cell and font formatting while the other formats does.

How to Set Conditional Formatting Icons in Google Spreadsheet

Friday, August 5, 2016, Hari

We say, one image worth 1000 words. Similarly visual representations are sometimes more meaningful than raw data. Conditional formatting is a life saving feature we find in spreadsheets. It allows to format cells based on some condition. In MS Excel, conditional formatting allows background colors, data bars, color scales and icon sets. Among all, icon sets is a cool feature which allows to display various icons in a cell according to condition.  
In Google Spreadsheet, conditional formatting allows background and font formatting, icon sets are not yet supported. But there is a workaround which can help you to apply different icons based on condition similar to MS Excel icon sets.

How it works ?

We can apply following two formula combinedly to achieve the similar feature.

  1. IF(logical_expression, value_if_true, value_if_false) 
  2. IMAGE(url, [mode], [height], [width]) 

You can upload your icons in any server or any 3rd party sites available on internet. Then apply your formula as shown in below example. 

Above formula will render two different images based on the condition A3<50 
You can even use nested IF when you have more than 2 conditions. Here is an example: 

Above formula will show three different icons based on the cell value "Yes", "No" and "May be". You can also mark the nested IF present in place of ELSE of first IF. Here is an example worksheet to play around.

Conditional Formatting Icons

Bonus tips 

  • You can check in the formula IMAGE("your_icon_url",3) contains a 2nd parameter 3. It is an optional parameter and indicates the sizing mode of the icon. So, 3 is to display the original size of the icon. This way you can ensure that the icon rendering will not stretch size or look blur. 

  • If you are playing with cells which contains letters then it is better to wrap the cell value with LOWER(text), so that it can handle both upper and lowercase letters. For example, "YES" and "yes". 

  • In MS Excel there is an option to "show only icons", which will hide the textual data and display only the icon in the cell. Though there is no direct way to achieve this in Google Spreadsheet but you can always hide the textual column to get the same type of experience. 

Desktop Notification From Browser Using Google Spreadsheet

Thursday, July 28, 2016, Geetanjali

These days many sites have implemented browser notifications. Its a new HTML5 feature which can show a quick alert on desktop. Of course users have to first allow the permission for the notifications to be displayed. You can see browser supports for notifications here .

What a Browser Notification displays ?

Browser Notification

A notification is a small rectangular shaped box comes either at top or bottom right depending on the browser and OS. It looks really cool with its icon and text. It has following information:

  • Icon: A square icon displaying the context of the notification
  • Title: Contains a few words about the announcement
  • Description: A small description about the event/product
  • Source: The URL from which the notification comes

 

How it works ?

We have created an app which can help you to drive notifications from your Google Spreadsheet. It is very easier to edit a spreadsheet and add a notification rather than changing the code. Announcing a product release or asking for a review is way simpler than you ever thought. Just you need to update the Google Spreadsheet and of course you will have to append a row at the beginning.

In the Google Spreadsheet you can find 5 fields: Unique id, Title, Description, URL and Icon. Unique ID is the column which should be always unique, otherwise users won't see the notification. In the icon column you can put your own site's logo or favicon.

  • Make a copy of this Google Spreadsheet
  • Go to File → make a copy...
  • Put your details in the columns and publish the spreadsheet
  • To publish go to File → Publish to the web.. → Publish
  • Copy the URL present in that popup.
  • There is a variable called  "URL" present in the downloaded script. Put the copied URL in that variable.

The code contains a JavaScript part which will fetch the info from the published sheet in JSON. It will check whether the unique id of newly added data (from the spreadsheet) is present in the local storage or not. If it does not find then it will show the notification.

How Spreadsheet data is fetched with JavaScript ?

This uses the JS script https://www.google.com/jsapi . We can query a spreadsheet with SQL like syntax. So in this case the script fetches always first two rows. To do that it uses OFFSET and LIMIT.

  • OFFSET – to ignore first N number of rows
  • LIMIT – To return N number of rows

So the query becomes 'SELECT A,B,C,D,E LIMIT 2 OFFSET 1'
You can know more about query language references here.