Send Notification Email If a Cell is Changed in Google Spreadsheet

Monday, September 12, 2016, Geetanjali

The live collaboration feature has turned the Google Spreadsheet into an awesome spreadsheet tool. We share our spreadsheets to multiple people with different level of access. Many times it happens that we want to be get notified when a cell in a specific range is updated. We need that updated value to be emailed to us. We may need the entire row or entire column or a custom range data in the notification email. Keeping all these in mind we have created a script which will do this job.

How it works ?

The script is made with Google Apps Script. There is an onEdit trigger which is triggered once the sheet is updated. You can set a required range with A1 notation (lets say A2:E15). Hence, the trigger will work only if the edited cell lies in the required boundary.

Notification Data

Settings and configurations:

Following parameters you can configure in your script.

  1. REQUIRED_RANGE - The range to track. If the edited cell lies in this range  the notification will be sent.
  2. EMAIL_RECIPIENT - The email address to whom the notification will be sent.
  3. EMAIL_CONTENT_TYPE - What to send in the notification, entire row/entire column/custom range data.
  4. CUSTOM_RANGE - Range data to be sent in the notification email
  5. EMAIL_SUBJECT - Subject line of the notification email
  6. SHOW_LAST_MODIFIED - Takes true/false. Decides whether to display an inline note in a cell about its last modified time.

The body of the email contains the range data with edited cell highlighted.

Notification Data

Purchase and download

  $15
Basic (Version1)
$23
Advanced (Version2)
Send notification email YES* YES
*Content of the email Only the edited cell content Can be the entire row, entire column, edited cell content or a custom range 
Show last modified time YES YES
Customize email parameters like to and subject YES YES
 
Get the app
Get the app

Customization and modifications

It may possible that you will need some additional customizations on the script to make it fit to your need. You can always request us for a customizations. Drop an email at support@tabgraf.com we will reply you within 24 hours. Example: Copying a range to another sheet, creating a new spreadsheet or creating an event in Google Calendar once a cell is edited

Custom Funtion to Get Place from Zip Code in Google Spreadsheet

Friday, September 9, 2016, Geetanjali

Functions/formula are the most friendly way to quickly calculate something in a spreadsheet. There are a lot of in-built functions available in Google Spreadsheet, check here the list of all functions available. Google spreadsheet has also an option called custom function. When a calculation is repeatedly required at multiple places we can create a custom function for it. Just like an in-built function a custom function can be directly invoked from a cell and can receive a parameter. The returned value from the function is displayed in the cell.

Custom Function

Geo data custom functions

We have created a list of custom functions to easily drag some frequently used geo data such as capital from country name, address from zip etc. Here are the list of functions you have:

  1. CAPITAL_FROM_COUNTRY_CODE
    This will return the name of the capital if country code is provided.
  2. PHONE_FROM_COUNTRY_CODE
    This will return phone code if country code is provided.
  3. CURRENCY_FROM_COUNTRY_CODE
    Returns currency code from country code.
  4. CAPITAL_FROM_COUNTRY_NAME
    Returns capital from country name
  5. PHONE_FROM_COUNTRY_NAME
    This will return phone code from country name
  6. CURRENCY_FROM_COUNTRY_NAME
    This will return currency code from country name
  7. PLACE_FROM_ZIP_CODE
    Returns place from zip code. It may return multiple places so you can append country name with the zip code to get a place in a particular country.
  8. COORDINATE_FROM_PLACE
    This will return the coordinate (latitude and longitude), if the place name is provided.

How to setup

  • Make a copy of this spreadsheet
  • Go to tools → script editor, you can see all the custom functions
  • For testing, you can play with the spreadsheet you just created. To use in other spreadsheets, you can copy paste the code in script editor.
  • The first line contains the API_KEY. This is the api key to get geocoding data which uses Google API. Get an api key from here https://developers.google.com/maps/documentation/geocoding/start
  • If you are using it for the first time, you need to authorise the script. Just select the test() function in the script editor and click run.

The last two functions PLACE_FROM_ZIP_CODE, COORDINATE_FROM_PLACE uses zip and place as parameters respectively. You can always provide zip code in addition to country name or place to get more specific results. You can also pass a cell as a parameter like usual spreadsheet functions. Example: =CURRENCY_FROM_COUNTRY_CODE(A9)

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.