Hiding certain cells in Google Spreadsheet based upon values in other cells

How to hide/show rows in a Google Spreadsheet based upon values of other cells.

1)  First off watch the video on YouTube.  The video shows you how to Hide certain cells in Google Spreadsheet based upon values in other cells.

2)  The code required for this project uses "Named Ranges" in Google Spreadsheets.  (tip: If you are unsure on how to create a script see steps 2-4 on this blog post on Create a custom drop down menu based upon values in a Google Spreadsheet.

3)  Create a new script and paste in the following code. (tip: If you are unsure on how to create a script see steps 3-5 on This Blog Post on How to Send a Custom Email from a Google Script)

function HideColumns() {

  //open the current spreadsheet and get the value of the named range

   var ss = SpreadsheetApp.getActive();

   var name = ss.getRangeByName("VatReg");

   var namevalue = name.getValue();

   var scheme = ss.getRangeByName("VATscheme")

   var schemevalue = scheme.getValue();

  

  //check the named range and if the value is equal to no then hide the requested columns

  if (namevalue == "NO") {

    var sheet = ss.getSheetByName("Entries");

    sheet.hideColumns(9);

    sheet.hideColumns(11);

    //on sheet Setup hide the 3 rows as requested    

    var named = ss.getSheetByName("Setup");

    var range = sheet.getRange('A7');

    Logger.log(range);

    named.hideRows(range.getRowIndex());

    range = sheet.getRange("A8");

    named.hideRows(range.getRowIndex());

    range = sheet.getRange("A9");

    named.hideRows(range.getRowIndex());

  }

  

  //OTHERWISE make sure the colums are showing

  else if (namevalue != "NO") {

    var sheet = ss.getSheetByName("Entries");

    sheet.showColumns(9);

    sheet.showColumns(11);

    

    //on sheet Setup show the 3 rows as requested    

    var named = ss.getSheetByName("Setup");

    var range = sheet.getRange("A7");

    named.showRows(range.getRowIndex());

    //named.unhideRow(range); //.getRowIndex());

    range = sheet.getRange("a8");

    //named.unhideRow(range.getRowIndex());

    named.showRows(range.getRowIndex());

    range = sheet.getRange("A9");

    named.showRows(range.getRowIndex());

//    named.unhideRow(range.getRowIndex());

  }

  

  //hide column J if VatReg is NO or VATscheme FLat Rate

  if ((namevalue == "NO") || (schemevalue == "Flat rate")) {

    sheet.hideColumns(10);

  }

  //otherwise show the column

  else {

    sheet.showColumns(10);

  }

  

}

4) Create the following trigger

UI Services in Google Apps Script

In this post I review the basics of creating user interfaces in Google Apps Script.  Addtionally, since most of this code is semi complex and may be the first time that many of you have seen these function calls I will be explaining all of the code on a line by line basis starting with step 6.

Due to the complexity of this item and the lack of documentation I've created a video titled Google Apps Script Tutorial How to create UI using Apps Script 

1)  Create a new script and paste in the following code. (tip: If you are unsure on how to create a script see steps 3-5 on This Blog Post on How to Send a Custom Email from a Google Script)


function BuildUI() {
  //create the application itself
  var app = UiApp.createApplication();
  app.setTitle("My Frist UI Interface");

  //create panels and add them to the UI
  var panel = app.createVerticalPanel();

  //create a text box
  var text1 = app.createTextBox();
  text1.setName('TextBox1');

  //create a submit button
  var button = app.createButton('Please Click Me');

  //add the text box and the button to the panel
  panel.add(text1);
  panel.add(button);

  var handler = app.createServerHandler("submitButton");
  button.addClickHandler(handler);
  handler.addCallbackElement(panel);
 
  //add the panel to the application
  app.add(panel);

  var doc = SpreadsheetApp.getActive();
  doc.show(app);
}

function submitButton(e){
  var app = UiApp.getActiveApplication();
  var textValue = e.parameter.TextBox1;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([textValue]);
  return app.close();
}

2.  SAVE the work and then select the Build UI Function:

3.  Run the function..

4.  Go BACK to your spreadsheet and then do the following..

5.  It works!!

6.  Line by line breakdown of the UI code...

var app = UiApp.createApplication();

This used to Create a new UiInstance, which you can use to build a UI.  A "UiInstance" referrers to the entire user interface itself. 

app.setTitle("My Frist UI Interface");

This sets the title of the application itself.  If this application will be run as a service, this will be used as the title of the page. If this application will be run as a dialog (for example inside a spreadsheet) it will be the title of the dialog's window.

var panel = app.createVerticalPanel();

This creates a panel (which will later be added to the application) that lays all of its widgets out in a single vertical column.  There are a total of 16 different types of panels that all allow the widgets in your application to flow in different directions.  You can read about those panels here...

https://developers.google.com/apps-script/reference/ui/ui-instance

var text1 = app.createTextBox();

text1.setName('TextBox1');

This creates a text box which is pretty self explanatory.  The important line of code here is the ".setName('TextBox1');"  Without setting the name you would never be able to later access the text box.  In the created function "submitButton(e)" you can reference the value that the user has typed in to the text box by calling "e.parameter.TextBox1"  Note the same value name from ".setName('TextBox1');" and "e.parameter.TextBox1"

var button = app.createButton('Please Click Me');
This creates a submit button for the form which again is very self explanatory.  In this line of code the "Please Click Me" just references the wording on the actual button but does NOT represent a variable name for the button.  IF you needed to reference a certain button later you could use the following when declaring...
var button = app.createButton('Please Click Me').setId("submit");
and then in function "submitButton(e)" you could reference the above item by using the id like this..
var subButton1 = app.getElementById(“submit”);

subButton1.setStyleAttributes({background: "black", color: "green"});
Note the same id in the following 2 items...
".setId("submit");" and ".getElementById(“submit”);"

panel.add(text1);

panel.add(button);

These 2 lines add the actual items to the vertical panel.  Without adding the items in they would not be visible to the end user and would continue to simply be variables that you could manipulate.

var handler = app.createServerHandler("submitButton");

This creates a server-side click handler object called handler as part of the UiInstance application called app. A click handler performs an action in response to a mouse click. Server-side means that the actions are performed by a server, in this case Google's Apps Script server. The argument ('submitButton' ) means the handler action is to run function submitButton(e).  Note that in the past Google scripts had a separate handler specific to buttons that was referred to as "createServerClickHandler()" but that function has since been deprecated and you should not use "createServerHandler()" instead.

It should be noted that you can add multiple click handlers to a button. The calls to the click handlers are asynchronous. That means that after the browser has requested that the server run a click handler function, it doesn't wait for a response, and continues immediately to the next line of code. This is important, because browser JavaScript is single-threaded, and if the code waited for the server to respond, nothing else could happen until the response came back, including users clicking on buttons in the app or doing anything else. This also means that server function calls may not execute in the order you expect.

button.addClickHandler(handler);

This associates the click handler to the already-defined button object called button.  Without this association the clicking of the button would not like to the defined server handler variable which was declared as "handler."

handler.addCallbackElement(panel);

This line is adds all widgets that have previously been added to the panel (in this case text1 and button) to this ServerHandler as a "callback element."  When a ServerHandler is called, any widgets added here, as well as any current child widgets of those widgets, are considered as callback elements. The value of each of these widgets is added to the information sent to the script processing the event.

app.add(panel);

This adds the panel AND all items that have previously been added to the panel to the actual application itself.  Without adding the panel to the application the panel and its associated items would not be visible to the end user and would continue to simply be variables that you could manipulate.

var doc = SpreadsheetApp.getActive();

doc.show(app);

This gets the actual spreadsheet that is open and then shows the application to the end user.

function submitButton(e){

This is how you create a JavaScript function, which will be a custom Spreadsheet function named submitButton. Notice that when you are creating a function which is intended to be called as a custom Spreadsheet formula, you tell the function to accept some input value (in this case "e").  Appscript generates this object and passes along to submitButton function.  Using object e we can get the value of user-submitted variables by later referencing the variables by referencing "e.parameter..."

var app = UiApp.getActiveApplication();

This gets the active UiInstance (application).

var textValue = e.parameter.TextBox1;

This creates a variable "textValue" and stores the user submitted variable from the text box within the variable "textValue."

var sheet = SpreadsheetApp.getActiveSheet();

This gets the active SHEET from within the active spreadsheet that is open.

sheet.appendRow([textValue]);

This writes the value of the variable "textValue" to the next line of the open sheet as referenced above.

return app.close();

This closes the application itself and returns you to normal view of the active spreadsheet.

7.  Finished!

Nicely done!!  Be sure to check out the rest of the blog for tips on writing custom code for Google Docs and Google Apps.

Google Form: Create a custom drop down menu based upon values in a Google Spreadsheet

How to create a custom drop down menu in a google form based upon values in a Google Spreadsheet.

1)  Create a Google Form WITH a list item and set the form to automatically post the results to a spreadsheet.  For this example code will be based upon this tutorial on how to Create a Basic Google Form.  Purely for this example the list item name is going to be called "Names of People."

2)  Here is a quick screen shot of the form.  Note that the choices of A,B,C,D and E were items that were manually entered in when creating the form in google drive.

3) Now create a NEW spreadsheet and in that sheet create a list of names like this...

4)  Create a NAMED RANGE by doing the following...

5)  Create a new script and paste in the following code. (tip: If you are unsure on how to create a script see steps 3-5 on This Blog Post on How to Send a Custom Email from a Google Script)

function editForm() {

  

  //you ONLY need to change (at MOST) the next 3 lines.. 

  var formID = '0123456789ABCDEFGHIJKLMNOP' //this is the ID of the form you are using

  var NameInForm = 'Names of People' //This is the Name from the form (steps 1 & 2)

  var NamedRange = 'CustomNames' //This is the NamedRange (step 4: item #4)

  

  // open the form and then get all items from inside the form.   

  var form = FormApp.openById(formID);

  AllFormItems = form.getItems();

  // loop through all of the items in the form

  for (i=0; i < AllFormItems.length ; ++i) {

    FormTitle = AllFormItems[i].getTitle();

    

    // Compare the title to that of each item in your form

    if (FormTitle == NameInForm) {

      FormItemId = AllFormItems[i].getId();

    }

  }

  

  // If the item was not found then exit

  if (FormItemId == 0) {

   return; 

  } 

  

  // Casting item to listItem because the form is using a drop down. 

  FormItemList = form.getItemById(FormItemId).asListItem();

  

  // Getting object of current spreadsheet from which we need

  // to get values for drop down list.

  ss = SpreadsheetApp.getActive();

  // Getting the items in your named range from the spreadsheet

  SheetItemRange = ss.getRangeByName(NamedRange);

  SheetItems = SheetItemRange.getValues();

  

  // Create the array of choices and loop through each of them

  var SheetItemChoices = []

  for (i=0; i < SheetItems.length; ++i) {

    

      SingleSheetItem = SheetItems[i][0].toString().trim();

      if (!(SingleSheetItem=='')) {

        SheetItemChoices.push(FormItemList.createChoice(SingleSheetItem));

      }

    }

   

  // Setting the choice array to drop down item.

  FormItemList.setChoices(SheetItemChoices);

}

6)  Create the following trigger

7)  Manually update your spreadsheet and then go look back at the form.  It will have automatically updated itself and look like this...

Send a custom email from a Google Form Submission

Below are detailed steps on how you can send a customized email from a Google Docs form submission.

1)  Create a Google Form and set the form to automatically post the results to a spreadsheet.  For this example code will be based upon this tutorial on how to Create a Basic Google Form.

2)  In your Google Drive open the spreadsheet where the form responses are being stored.
Tip:  You can access you google drive by going to https://drive.google.com

3)  After the form is open go to the script manager by doing the following.  

Tools > Script Editor.

Click on "Blank Project"

4)  DELETE ALL of the code appearing in the Code.gs box.

5)  Replace it with all of the following code..

function sendMessage(e) {

   //Capture the items from the form
   var firstName = e.values[1]; //declare a variable storing the first name
   var lastName = e.values[2]; //declare a variable storing the last name
   var emailAddress = e.values[3]; //declare a variable storing the user's email address

   var wantsEmail = e.values[4]; //declare a variable storing whether or not they want an email
   var birthday = e.values[5]; //declare a variable storing the user's birthday

   //set up variables to be used to send out the email
   var fromName = "Company USA Inc."; //the "From" name you want to be displayed in the email
   var replyTo = "Custom@YourDomain.Com"; //you can specify a reply to address if you like
   var Subject = "Form Submission response"; //the subject of the email to be sent out

   //start building the body of the message
   //There is no error checking here and will just assume all fields contain values
   var Body = "Dear " + firstName + " " + lastName + ",\n\n" +
       "Thank you for filling out our web form.\n" +
       "We hope you enjoy your birthday on " + birthday + "\n" +

       "We look forward to speaking with you soon.\n\n" +

       "Regards,\n" +
       "Your Name Here";

   //Do a very basic if statement to see if an email should actually be sent
   //(In theory ALL of the above items associated with the email should be INSIDE this If statement.
   //However, this is just a very basic learing tutorial for you to start with
   if (wantsEmail == "Yes")
   {
       //build and send out the email
       MailApp.sendEmail(emailAddress, Subject, Body,

       {name:fromName, replyTo:replyTo});

   } //end of the if statement

}//end of the function

6)  Save and then name the file

7)  Set up a trigger to make the code run whenever someone submits a form.

Then verify you have the following settings...

Finally authorize the script to run

8)  An email will be automatically sent to every user that submits the form.  In gmail it will look similar to this.

FINISHED!  Nicely done.  Be sure to check out the rest of the blog for tips on writing custom code for Google Docs and Google Apps.

Be sure to post any questions / comments below.

How to Create a Basic Google Form

Ok, lets start with the very basics of google forms.  Below is a detailed step by step process on how to create a google form that will post the results of the form to a google spreadsheet.  After you get this completed you can view this tutorial on how to Send a custom email from a Google Form Submission or view home page of the blog showing how to customize google forms and apps.  To get started all you need is a basic, free, gmail account.

Step 1:  Log into your gmail account.

Step 2:  Click on "Drive" link at the top of the page

Step 3:    On the left side of Google Drive click first on "Create" and then click on "Form."

Step 4:  Choose a name of the form you want to use as well as which theme.  For this example Default theme was chosen along with a generic name of "Sample Form."

Step 5:  You will want to capture all data from this form into a spreadsheet. 

1)  Click on "Choose response destination" at the top

2)  Verify "New spreadsheet" is checked and then click on "Create."

Step 6:  Create the first form field.  Here is an example text field with some details.

Step 7:  Fields were created for "First Name", "Last Name" and "Email."  Next a multiple choice field for a "Yes / No" question is going to be added.  Here are the steps to create a multiple choice box.

Type in the question you want to ask along with the possible responses.

Step 8:  Finally add a field to capture a date.

Type in the question you want to ask along with the necessary options for the end user to see.

Step 9:  Finish up your form with the following guidelines.

Step 10:  Whenever someone submits the form it will store in the spreadsheet like this.

Per "Step 5" in this tutorial the sheet with all of the responses was named "Sample Form (Responses)" if you open your google drive (https://drive.google.com/) you will see the spreadsheet storing all of the responses.  The contents will look similar to to this.

Congratulations you have just completed your first form.  For advanced tips on how to further automate portions of your form including sending email or analyzing data please see the main page of this blog or this tutorial on how to Send a custom email from a Google Form Submission.

View a Live Sample of the above created form.