copy

Wednesday, 28 November 2018

Last updated: 08/12/2018, 3 min read (without the code)

Send Multiple Emails From Google Sheets Using GAS


Introduction



Have you ever wondered how is it possible to send multiple emails to different recipients, without counting on external services, like MailChimp, AWeber, Mailerlite and others? If the answer to this question is yes, then you would probably be surprised to learn that you can achieve this by simply using Google Sheets and some custom code written in Apps Script.

In this post, we will assume that you already have the email addresses to which you want to send emails. In another post, we will see how to retrieve your Gmail contacts in a Google Sheets spreadsheet. Warning: the process below was not written to make you a spammer! The fair usage of the script that follows is up to you!



The process



The entire process involves 14 steps. However, once you set up the spreadhseet, then it is as easy as clicking a button on the menu bar.

Step 1: Following the instructions described here (steps 1 to 4) create a new empty spreadsheet on your Google Drive.

New Empty Spreadsheet

Step 2: Open the new spreadsheet in Google Sheets.

Step 3: Rename the first sheet to Emails by double-clicking upon the Sheet1 name and entering the suggested name (Emails). Of course, you can enter another name, if you want. You just have to be careful to put the same name in the code as well.

Rename Sheet

Step 4: Add the headers to the sheet so as to look like the image below. I think that the headers are self-explanatory.

Adding the Headers In The Spreadsheet

Step 5: Switch to the Script Editor by selecting the Tools category from the menu and then clicking on the Script editor option.

Open The Script Editor

Step 6: In the Script Editor, paste the code that you will find below.

Step 7: Click the Save button or select the File category from the menu and then click on the Save option. Alternatively, you can use the CTRL + S shortcut.

Save The Project

Step 8: In the Edit Project Name form that will pop-up, enter a name and click the OK button.

Editing The Project Name

Step 9: Switch back to the spreadsheet and refresh the page (note that when you refresh the spreadsheet page, the script editor’s page will automatically close). You will see a new option on the menu called Custom Code.

Additional Menu

Step 10: Fill the rows below the headers with the appropriate information (email address, email subject and email main message). The other four columns (D through G) are optional and can be used for customizing even more your emails.

Filling The Rows With Information

Step 11: Then, select the Custom Code category from the menu and click on the Send Emails option.

Send Emails Option

Step 12: In the Authorization Required form that pop-ups, click on the Continue button.
Authorization Required Form

Step 13: In the Sign-in form that pop-ups, click on your Google account.

Sign In Form

Step 14: Finally, in the next form that appears, click the Allow button.

Allow Access To Google Account

If all the previous steps performed successfully, you will probably see something like this:

Emails Were Sent Successfully

And here are the emails that were sent (in the inbox of the recipient):

Emails Received

Bonus Tip: To create a line break within a cell that contains your main message, simply use CTRL + ENTER instead of ENTER.



GAS code



Here is the GAS code that loops from row 3 till the last row and automatically sends the emails to the appropriate recipeints. Read the code comments for more information.

  /*
  ------------------------------------------------------------------------------
  Written By:    Christos Samaras
  Date:          28/11/2018
  Last Updated:  08/12/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  ------------------------------------------------------------------------------
  */

function onOpen(e) 
{
  /*
    ------------------------------------------
    Creates a custom menu at the spreadsheet.
    ------------------------------------------
  */
  
  SpreadsheetApp.getUi()
      .createMenu('Custom Code')
      .addItem('Send Emails', 'sendMultipleEmails')
      .addToUi();
}

function sendMultipleEmails() 
{
  /*
    -------------------------------
    Sends multiple emails at once.
    -------------------------------
  */
  
  // Ge the sheet containing the data by its name.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails");  
  
  // The name you want to be displayed in the recipient's inbox.
  var displayedName = "Name Surname"
  
  // Check that the sheet object is not null (i.e. the sheet name is correct).
  if(sheet == null)
  {
    Browser.msgBox("Invalid sheet name!");
    return;
  }
  
  // Set the first row that containing the data (after headings).
  var startRow = 3;
  
  // Get the last row containing data (in column A).
  var lastRow = sheet.getRange("A1:A").getValues().filter(String).length;
  
  // Check that the last row is greater than the start row.
  if(lastRow < startRow)
  {
    Browser.msgBox("There are no emails to send!");
    return;
  }
  
  // Loop through all the rows and send the emails.
  for (var i = startRow; i <= lastRow; i++)
  {
    // Get the email address.
    var address = sheet.getRange(i, 1).getValue();
    
    // Get the email subject.
    var subject = sheet.getRange(i, 2).getValue();
    
    // Check if the welcome message (i.e. the optional parameter) is not empty.
    if(sheet.getRange(i, 4).getValue() != "")
    {
      // Create the custom message with the names, along with the welcome and the goodbye message.
      var message = sheet.getRange(i, 4).getValue() + " " + sheet.getRange(i, 5).getValue() + ",\n\n"; // Welcome greeting.
      message+= sheet.getRange(i, 3).getValue() + "\n\n";                                              // Main message.
      message+= sheet.getRange(i, 6).getValue() + "\n" + sheet.getRange(i, 7).getValue();              // Goodbye greeting.
    }
    else
    {
      // This is the standard message (no optional parameters are provided).
      var message = sheet.getRange(i, 3).getValue();
    }
    
    // Send the email using the Gmail service (typical parameters).
    // GmailApp.sendEmail(address, subject, message);
    
    // To include your name, call the Gmail service like this:
    GmailApp.sendEmail(address, subject, message, {"name": displayedName});
    
    // Other options include bcc, cc, attachments, noReply, replyTo etc.
    // See here for more information:
    // https://developers.google.com/apps-script/reference/gmail/gmail-app
        
    // An alternative method that has some issues, is this:
    // MailApp.sendEmail(address, subject, message);
  }
  
  // Inform the user about the process.
  Browser.msgBox(((lastRow - startRow + 1) == 0 ? "An email was" : (lastRow - startRow + 1) + " emails were") + " successfully sent!");
}

More information about the GmailApp object can be found on the developer's page.

Tuesday, 30 October 2018

Last updated: 07/11/2018, 2 min read (without the code)

Creating & Customizing Column Charts In Google Sheets



Introduction



Google Sheets, much like Microsoft Excel, incorporate many different chart types, which can help the user to visualize his/her data. In this post, we will learn how to create and customize a simple column chart in Google Sheets. There will be the “manual way”, the “coding way” – using Google Apps Script – as well a bonus tip for selecting chart colors.



The manual way



To create a column chart manually just follow these 2 simple steps:

Step 1: First, select the range containing the data, e.g. A1:C4.

Range Selection

Step 2: Then, go to the menu Insert and click the Chart option.

Insert Chart

The generated chart would look like this:

Default Column Chart

To customize the chart, right-click upon the chart, and on the context menu that pop-ups, select the appropriate option. For example, to customize the chart title, you have to select the Chart & axis titles option and then the Chart title option.

Customizing Chart Title

The Chart editor form appears, where you can customize several options. Here, for example, we have changed the text, font, font size, format and the text color of the title.

Chart Editor

In a similar way, you can customize every available chart option (e.g. series, legend, axes and gridlines).



The coding way



The manual way can be quite daunting and time-consuming, especially if you have to customize several chart options. Thanks to Apps Script, though, the same process can be automated, allowing you to create beautiful charts in a few seconds. The code below contains probably the most common options that someone will need to customize when creating a column chart. It has the following options:
  • Generic chart options: chart position and size.
  • Chart title options: text and text style.
  • Legend options: position and text style.
  • X-axis options: title and text style.
  • Y-axis options: title, gridlines and text style.
  • Series options: color, data labels, error bars and text style.
  • Trendline options: type and line style.
Most of these options are set using mainly the setOption method of the chart object. The various options are passed using a CSS syntax. Additional options can be found on this page.

function createColumnChart() {

  /*
  ------------------------------------------------------------------------------
  The function creates a custom column chart in the active sheet using the data
  that exist in the range that is specified in the dataRange variable.
  
  Written By:    Christos Samaras
  Date:          30/10/2018
  Last Updated:  07/11/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  ------------------------------------------------------------------------------
  */
  
  // Range to get the data.
  var dataRange = 'A1:C4';
  
  // Variables for customizing colors.
  // Use this tool to generate "good" colors:
  // http://paletton.com
  var color1 = '#EE295A';   // Fill and data label color for the first series.
  var color2 = '#FFD42C';   // Fill and data label color for the second series.
  var color3 = '#8D7CEE';   // Trendline and axis titles color.
  var color4 = '#A6F870';   // Gridlines, axis labels and chart title color.
  
  // Text for titles (chart and both axes).
  var chartTitle = 'Vehicles Comparisons';
  var xAxisTitle = 'Vehicle Category';
  var yAxisTitle = 'Speed & Power';
  
  // Get the active sheet.
  var spreadsheet = SpreadsheetApp.getActive();  
  var sheet = spreadsheet.getActiveSheet();
  
  // Create a chart.
  var chart = sheet.newChart()
  .asColumnChart()
  .addRange(spreadsheet.getRange(dataRange))
  
  // Set the generic options.
  .setNumHeaders(1)
  .setBackgroundColor('#333333')  
  //  .setOption('is3D', true)   // For a 3D chart.
  //  .setOption('height', 400)  // Custom height. 
  //  .setOption('width', 600)   // Custom width.
  
  // Set the chart title options.
  .setOption('title', chartTitle)
  .setOption('titleTextStyle', {
    color: color4,    
    alignment: 'center',
    fontName: 'Verdana',
    fontSize: 24,    
    bold: true    
  })
  
  // Set the legend options.
  .setOption('legend', {
    position: 'top',
    alignment: 'center',
    textStyle: {
      color: color3,
      fontName: 'Verdana',
      fontSize: 12,
      bold: true
    }
  })
  
  // Set the X-axis options.
  .setOption('hAxis', {
    title: xAxisTitle,
    titleTextStyle: {
      color: color3,
      alignment: 'center',
      fontName: 'Verdana',
      fontSize: 16,
      bold: true
    },
    textStyle: {
      color: color4,
      fontName: 'Verdana',
      fontSize: 14,
      bold: true
    }
  })

  // Set the Y-axis options.
  .setOption('vAxes', {
    0: {
      title: yAxisTitle,
      format: 'none',
      titleTextStyle: {
        color: color3,
        alignment: 'center',
        fontName: 'Verdana',
        fontSize: 16,
        bold: true
      },
      textStyle: {
        color: color4,
        fontName: 'Verdana',
        fontSize: 14,
        bold: true
      },
      gridlines: {
        count: -1,
        color: color4,
        width: 5
      }
    }
  })

  // Set the series options.
  .setOption("series", {
    0: {
      labelInLegend: 'Speed [km/h]',
      color: color1,      
      hasAnnotations: true,
      dataLabel: 'value',
      dataLabelPlacement: 'outsideEnd',
      errorBars: {
        magnitude: 20,
        errorType: "percent"
      },
      dataLabel: "value",
      textStyle: {
        color: color1,
        fontSize: 12,
        fontName: 'Verdana',
        bold: true
      }
    },
    1: {
      labelInLegend: 'Power [kW]',
      color: color2,
      hasAnnotations: true,
      dataLabel: 'value',
      dataLabelPlacement: 'outsideEnd',
      errorBars: {
        magnitude: 20,
        errorType: "percent"
      },
      dataLabel: "value",
      textStyle: {
        color: color2,
        fontSize: 12,
        fontName: 'Verdana',
        bold: true
      }
    }
  })
  
  // Set the trendline options (here only for the first series).
  .setOption('trendlines', {
    0: {
      color: color3,
      type: 'linear',
      lineWidth: 4,
      opacity: 0.8,
      labelInLegend: 'Trendline',
      visibleInLegend: true
    }
  })
  
  // Position: row and column of top left corner (cell E2 here).
  // Optional: offset X and Y in pixels.
  .setPosition(2, 5, 0, 0)
  
  // Finally, build the chart.
  .build();
  sheet.insertChart(chart);
};

If you run the above script on the same dataset (A1:C4), the final chart will look like this:

Column Chart With Code



How to select “good” chart colors



If you saw the chart that was generated from the previous Apps Script code you would probably notice that the color combination is quite “pleasing to the eye”.  The particular set of colors was selected using this tool. If you play with it for a few minutes, you will discover that it has an extensive set of options for generating color palettes and harmonies. If you don’t know where to start, simply click the randomize button at the top of the screen to start your color scheme.

Palleton

Unlike Excel, the chart colors in Google Sheets can be customized more extensively. Therefore, you can create more impressive charts if you are willing to experiment a little bit with the color combinations.

Sunday, 30 September 2018

Last updated: 05/10/2018, 3 min read (without the code)

Triggers & Events In Google Sheets


Introduction



According to the documentation, triggers let Google Apps Script run a function automatically when a certain event, like opening a document, occurs. Apps Script supports two types of triggers, simple and installable. Simple triggers are a set of reserved functions built into Apps Script, like the function onOpen(e), which is executed when a user opens a Google Docs, Sheets, Slides, or Forms file. Installable triggers, on the other hand, offer more capabilities than simple triggers but must be activated before use. They can call services that require authorization, they offer several additional types of events including time-driven (clock) triggers, and they can be controlled programmatically.

Both types of triggers let Apps Script run a function automatically if a certain event occurs.  When a trigger fires, Apps Script passes in the function an event object as an argument, typically called “e”. The event object contains information about the context that caused the trigger to fire. For example, the simple onEdit(e) trigger for a Google Sheets script that we will see below, uses the event object to determine which cell was edited.

Unlike VBA, in Google Apps Script there are only a few events that can be used to detect changes within an application. In this post, we will analyze two of the most useful ones, the onEdit and the onOpen. Examples will be given on Google Sheets application. We will also learn how to install a trigger, both manually and programmatically.



GAS code



In the code below there are seven GAS functions:
  • onEdit: A simple trigger that fires when the contents of a cell change. In the particular case, the range that is checked for changes is the "A1:A10" in the active sheet.
  • onOpen: A simple trigger that is actually a logger, which runs every time that someone opens the spreadsheet. It fills the current date/time in the last row of column B in the active sheet.
  • createSpreadsheetOpenTrigger: It creates an open trigger programmatically.
  • createTimeDrivenTrigger: It creates a time-driven trigger programmatically that runs every 5 minutes.
  • rangeIntersect: A helping function that returns true when two ranges intersect. This is the GAS implementation of the Application.Intersect method that exists in VBA/Excel.
  • greeting: A function that displays a message box in the browser.
  • myCounter: A function that finds the last row in column D of the active sheet and adds +1 to the previous value.

/*
  --------------------------------------------------------------------------------
  The next set of functions show how to use triggers and events in Google Sheets.

  Written By:    Christos Samaras
  Date:          29/09/2018
  Last Updated:  05/10/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  --------------------------------------------------------------------------------
*/

/*
  ----------------
  Simple Triggers
  ----------------
*/
function onEdit(e)
{  
  /*
  -----------------------------------------------------------------------------------------
  A simple trigger that fires after a cell change in the range A1:A10 of the active sheet.
  -----------------------------------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Set the range that has to be chekced for changes.
  var selectedRange = sheet.getRange("A1:A10");
  
  // Get the active cell from the event obejct.
  var activeCell = e.range;
  
  // Check if the active cell belongs to the range of interest. 
  // If yes, show a message box in the browser.
  if(rangeIntersect(activeCell, selectedRange)) 
    Browser.msgBox("Triggered after the cell " + activeCell.getA1Notation() + " changed!");   
}

function onOpen(e)
{
  /*
  -------------------------------------------------------------------
  A simple trigger that acts as a logger, filling the date/time that
  the spreadsheet was openned in the last row of the column B.
  -------------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get the last row in column B that contains values.
  var lastRow = sheet.getRange("B1:B").getValues().filter(String).length;
  
  // Set the current date/time in the next available row in column B.
  sheet.getRange(lastRow + 1, 2).setValue((new Date()).toLocaleString());  
}


/*
  ---------------------
  Installable Triggers
  ---------------------
*/
function createSpreadsheetOpenTrigger() 
{
  /*
  --------------------------------------------------------------------------
  Creates a trigger programmatically that fires when the spreadsheet opens.
  --------------------------------------------------------------------------
  */
  
  // Get the active spreadsheet (workbook in Excel terminology).
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Create a new trigger that will run when the spreadsheet opens.
  // The trigger will fire the greeting function.
  ScriptApp.newTrigger('greeting').forSpreadsheet(ss).onOpen().create();
}

function createTimeDrivenTrigger() 
{    
  /*
  ---------------------------------------------------------------
  Creates a trigger programmatically that fires every 5 minutes.
  ---------------------------------------------------------------
  */
  
  // Create a new trigger that will run every 5 minutes.
  // The trigger will run the myCounter function.
  ScriptApp.newTrigger('myCounter').timeBased().everyMinutes(5).create();
}


/*
  ------------------
  Helping Functions
  ------------------
*/
function rangeIntersect (rng1, rng2) 
{
  /*
  ------------------------------------------
  Returns true if the two ranges intersect.
  ------------------------------------------
  */
 
  // Check for empty objects.
  if(rng1 == null || rng2 == null)
    return false;
  
  // Compare the rows and columns of the two ranges.
  return (rng1.getLastRow() >= rng2.getRow()) && (rng2.getLastRow() >= rng1.getRow()) 
          && (rng1.getLastColumn() >= rng2.getColumn()) && (rng2.getLastColumn() >= rng1.getColumn());
}

function greeting()
{
  /*
  ---------------------------------
  It displays a hello message box.
  ---------------------------------
  */ 
  
  // Display hello in the browser.
  Browser.msgBox("Hello!");
}

function myCounter()
{
  /*
  ----------------------------------------------------------------
  It adds +1 in the last row of the column D of the active sheet.
  ----------------------------------------------------------------
  */
  
  // Get the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get the last row in column D that contains values.  
  var lastRow = sheet.getRange("D1:D").getValues().filter(String).length;

  // Check if the last row is 0. If yes, fill the number 1 in the cell D1.
  if(lastRow == 0)
  {
    sheet.getRange(lastRow + 1, 4).setValue(1);
    return;
  }
  
  // Add +1 in the next available row in column D.
  sheet.getRange(lastRow + 1, 4).setValue(sheet.getRange(lastRow, 4).getValue() + 1);
}

onEdit example

OnEdit Event Example

The above image shows what happens when the onEdit trigger runs. If the user changes the contents of the cell A6, a message box will pop-up in the browser. Of course, this simple event was written only for demonstration purposes.


onOpen example

OnOpen Event Example

When the user double-clicks to open the selected spreadsheet, the current date/time will be filled in the last row of column B of the active sheet. This event can be very useful for logging purposes.



How to install a trigger



The manual way

To manually install a trigger, you will have to go to the Script editor. There, from the menu, select Edit and then Current's project triggers.

Current Project Triggers

In the window that will pop up, click on the No triggers set up. Click here to add one now. hyperlink.

Add New Trigger
In the form that pop-ups, select the function you want to run and the appropriate event. In this example, the greeting function was selected from the dropdown list, along with the From spreadsheet and On open event. To save the trigger, click on the Save button. That's it!
Set An Open Trigger


The programmatic way

The programmatic way is probably as easy as the manual one. You just have to use the createSpreadsheetOpenTrigger function and adjust it to your needs (e.g. change the function that will be executed when the spreadsheet opens).

To set the trigger, simply click the Run option on the menu of Script editor and select the createSpreadsheetOpenTrigger function from the sub-menu. The function will run and the trigger will be set.

Run Function On GAS Editor


Authorization procedure

Note: the first time that you will try to install a trigger, either manually or programmatically, you will have to set authorization permissions. So, a message box will pop-up asking you to authorize the trigger. Just click the Review Permissions button.

Trigger Authorization

Select your Google account to continue.

Select Google Account

Finally, in the next form, click on the Allow button.

Allow Access To Google Account

Congratulations! You have successfully installed a trigger! Read here about the restrictions that installable triggers have.



Read also



Tuesday, 28 August 2018

Last updated: 28/08/2018, 1 min read (without the code)

Geocoding & Reverse Geocoding Functions In Google Sheets


Introduction



In the previous post, we learned the basics about developing and using a custom function in Google Sheets. It is time now to switch to something more advanced compared to our first example. We have seen in the past how to use Google’s geocoding services from VBA. Here we will see how to call these services using Google Apps Script (GAS). The main advantage when developing a GAS function is that, unlike VBA, you don’t need to explicitly get an API key. The overall approach is slightly different than in the case of VBA since there is a Geocoder class available in GAS.



GAS code



Below you will find the code for five different GAS functions:
  • getCoordinates
  • getCoordinatesArray
  • getLatitude
  • getLongitude
  • getAddress
I think that the function names are self-explanatory. It should be noted that the getCoordinatesArray is identical to getCoordinates, but it returns the latitude and longitude as an array, not as a single string. In addition, the getAddress function is performing actually reverse geocoding. The idea behind these functions is similar to VBA, so after reading the request status, then if it is "OK", the corresponding node is read and returned.

/*
  -------------------------------------------------------------------------------
  The next set of functions are used to perform geocoding and reverse geocoding.

  Written By:    Christos Samaras
  Date:          18/08/2018
  E-mail:        xristos.samaras@gmail.com
  Site:          https://www.myengineeringworld.net
  -------------------------------------------------------------------------------
*/

/**
 * This function returns the latitude and longitude of a given address using the Geocoder class. 
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude and the longitude of the given address.
 * @customfunction
 */
function getCoordinates(address)
{  
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitue/longitude variables.
  var lat = 0;
  var long = 0
  
  // Check if the response returned without a problem.
  if (geocoder.status == 'OK') 
  {
      // Retrieve the latitue/longitude information.
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  }
  
  // Return the latitue/longitude information as string.  
  return lat + ", " + long;
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the latitude and longitude of a given address as an array using the Geocoder class. 
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude and the longitude of the given address.
 * @customfunction
 */
function getCoordinatesArray(address)
{  
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitue/longitude variables.
  var lat = 0;
  var long = 0
  
  // Check if the response returned without a problem.
  if (geocoder.status == 'OK') 
  {
      // Retrieve the latitue/longitude information.
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  }
  
  // Return the latitue/longitude information as an array.  
  return [lat, long];
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the latitude of the given address using the Geocoder class.
 *
 * @param {A2} address A cell that contains an address.
 * @return The latitude of the given address.
 * @customfunction
 */
function getLatitude(address)
{        
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the latitude variable.
  var lat = 0;
  
  // Get the latitude if the response returned without a problem.
  if (geocoder.status == 'OK')   
      lat = geocoder["results"][0]["geometry"]["location"]["lat"];      
  
  // Return the latitude as double.
  return lat;
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the longitude of the given address using the Geocoder class.
 *
 * @param {A2} address A cell that contains an address.
 * @return The longitude of the given address.
 * @customfunction
 */
function getLongitude(address)
{    
  // Initialize the geocoder object.
  var geocoder = Maps.newGeocoder().geocode(address);
  
  // Initialize the longitude variable.
  var long = 0;
  
  // Get the latitude if the response returned without a problem.
  if (geocoder.status == 'OK')   
      long = geocoder["results"][0]["geometry"]["location"]["lng"];
  
  // Return the longitude as double.  
  return long;
}

// --------------------------------------------------------------------------------------------------------------

/**
 * This function returns the address from a given pair of latitude and longitude using the Geocoder class.
 * In other words, it performs reverse geocoding.
 *
 * @param {A2} lat A cell that contains a latitude value between -90 and +90 degrees.
 * @param {B2} long A cell that contains a longitude value between -180 and +180 degrees.
 * @return The address from a given pair of latitude and longitude.
 * @customfunction
 */
 function getAddress(lat, long)
{
  
  // Checking the input variables:
  // The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.   
  if(lat < -90 || lat > 90)
    return "Invalid Latitude";

  // Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
  if(long < -180 || long > 180)
    return "Invalid Longitude";
    
  var response = Maps.newGeocoder().reverseGeocode(lat, long);
  var address = "";
  
  if (response.status == 'OK')
    address = response["results"][0]["formatted_address"];
  
  return address;
}

All functions containing descriptions following the approach that was analyzed here. The getCoordinatesArray function, though, needs "special treatment" to get its results. Here is how you can call it:
a. Select two adjacent cells.
b. In one of the two cells, enter the formula:
 =TRANSPOSE(ARRAYFORMULA(getCoordinatesArray(A5))) 
Where A5 is the cell containing the address you need to geocode.

NOTE: In the case of multiple results (for example two cities sharing the same name), the above functions return the first occurrence, so be careful with your input.



Online examples



You can find a few examples online in this Google Sheets file.



Read also



How To Create & Use A Custom Function In Google Sheets

Sunday, 19 August 2018

Last updated:08/09/2018, 5 min read

How To Create & Use A Custom Function In Google Sheets


Introduction



Google Sheets, along with Google Docs and Google Slides are a spreadsheet, a word processor, and a presentation program respectively, all part of a free, web-based software office suite offered by Google within its Google Drive service. The three applications are available as web apps, mobile apps and desktop apps (on ChromeOS only). The applications are compatible with the corresponding Microsoft Office file formats, therefore, the user can save an “online file” to his desktop and then open it with the appropriate Office app.

To be more precise, the Google Sheets app is very similar to Excel. If you have ever used Excel, especially some pre-Ribbon version (Office 2003 and back), you will definitely understand how the Google Sheets app works. The similarities don’t stop only on the environment and the user interface. The Google Sheets app contains an internal script editor where the user can create his/her own custom functions, similar to VBA functions in Excel. The language that is used to create these custom functions in Google Sheets is called Apps Script. The Apps Script is also used in other Google apps, so it reminds a lot of the universal usage of VBA in the entire Office suite.

The Apps Script is actually the Google’s version of ECMAScript, which runs on its servers (not in the browser). Consequently, Apps Script can be considered as a browser-independent language. If you haven’t heard the ECMAScript in the past, don’t worry! You probably already know another “dialect” of it, called JavaScript. In short, Apps Script is based on JavaScript 1.6 with some portions of 1.7 and 1.8 and provides a subset of ECMAScript 5 API. According to Google, the Apps Script "provides easy ways to automate tasks across Google products and third party services”. This is the language that we will use in this tutorial.



Create a new spreadsheet on Google Sheets



Before we write our first custom function using Apps Script, we will learn first how to create a new blank spreadsheet on Google Sheets. So, please follow the next simple steps:

Step 1: First of all, ensure that you are logged in to your Google account.

Log In To Google Account

Step 2: Go to the Google Drive page.

Logged To Google Drive

Step 3: In this page, you should click on the New button. Then, on the drop-down menu select the Google Sheets option by clicking the small arrow. Finally, click on the Blank spreadsheet option.

Create A New Spreadsheet In Google Sheets

Step 4: A new spreadsheet is created and you are redirected to a new page. To rename and this spreadsheet, click on the Untitled spreadsheet title and enter your preferred name.

Rename The Spreadsheet

If you hit enter, the spreadsheet will look like the image below.

Renamed Spreadsheet

Note, that all the changes are automatically saved in Google Drive, so unlike Excel, you don’t have to manually save it. Here is what you will see on your Google Drive:

Renamed File On Google Drive



The Apps Script editor



Now that we have created and renamed our spreadsheet, we are ready to switch to our Apps Script editor. Select the Tools category from the menu and then click on the Script editor option.

Switch To Script Editor

A new page pop-up; that’s our Apps Script editor or if you prefer our Integrated Development Environment (IDE). Unlike the VBA IDE, it is relatively simpler, nevertheless, it includes sufficient functionality.

The Script Editor

The default project contains a single script file (Code.gs) and an empty function (myFunction), which we will modify in a little bit. Script files are similar to VBA modules. Multiple script files can be created and organized inside a single project (i.e. as it is in the case of a VB 6.0 project). Within each script file, you can write code that is accessible from all the script files included in the project. Unlike VBA, there is no option to restrict access using the Option Private Module.

If you need to rename the Untitled project, simply click on the title. An input box will pop-up, prompting you to give a name. Type your preferred name and click the OK button.

Rename The Project
Here is how the renamed project would look like.

The Renamed Apps Script Project



Creating your first Apps Script function



In this point, it should be highlighted that the custom function will be created inside a container-bound script that is part of our spreadsheet. The container-bound functions are equivalent to VBA functions that are part of an Excel spreadsheet. Bear in mind, though, that there are also standalone scripts, which are created on Google Drive and usually contain generic functionality that can be used across multiple Google apps. In other words, these scripts are not bound to a specific spreadsheet or document. However, we will talk about this kind of functions and scripts in a different tutorial.

Our first function will calculate the area of a circle, given its radius. The formula is very simple:
Area = π ∙ Radius²

Here is the complete code for the custom function:

/**
 * This function returns the area of a circle given its radius. 
 *
 * @param {A1} radius A cell that contains the circle radius.
 * @return The area of the circle given its radius.
 * @customfunction
 */
function circleArea(radius) {
  return Math.PI * Math.pow(radius, 2);
} 

Let’s analyze the code a little bit:
  • The circleArea is the name of the function that will be used from the spreadsheet. Here we followed the typical camel case notation (e.g. the practice of writing compound words or phrases such that each word or abbreviation in the middle of the phrase begins with a capital letter, with no intervening spaces or punctuation).
  • The radius is our input variable. Note that, unlike VBA, we don’t have to declare the variable’s type (e.g. double) since, in Apps Script, all the variables are of type var.
  • The code that starts with the return word is actually the mathematical formula translated into code. Ιn Apps Script, similar to JavaScript, we have to use an internal library called Math for some common mathematical expressions (as it is in the case of power and the constant pi - π).
  • Finally, the comments that are enclosed between “/*” and “*/” are optional, but very helpful since will be used by the Autocomplete feature, which we will see below.
Your first custom function is almost ready! As you can see in the image below, an asterisk appears next to the Code.gs file. Click the Save button or use the CTRL + S shortcut from your keyboard.

Custom Function Before Saving

Congratulations! You just created your first custom Apps Script function. Here is how the saved function would look like. The asterisk is gone and the function name will appear in the dropdown menu that contains the functions.

Custom Function Saved



Use the custom Apps Script function from the spreadsheet



You can now close the Apps Script editor and go back to the spreadsheet. In a random cell (e.g. B1) start typing the function name (=circleArea). If you just type =ci notice that the Autocomplete feature appears, showing the function description.

Typing The Function Name

If you click with the mouse upon the Autocomplete window, or simply press the Tab key, you will see the full function description. This is the reason why it is a good practice to fill the function description in the script editor.

Full Function Description

If you type =circleArea(5) and click the Enter, you will see a Loading message and after a few seconds, the actual result in the cell (78.53981634).

Custom Function With Value

Of course, apart from manual values, we can use the function with cell references. In the example below, we use the function with the cell A4.

Custom Function With Cell Reference

Well done! You have successfully created and used your first custom Apps Script function. More tutorials on Apps Script will follow soon. Meanwhile, if you need more information about the custom Apps Script functions, you can visit the official page.



Read also



Geocoding & Reverse Geocoding Functions In Google Sheets