[How To] – Google spreadsheet trigger

Scenario: 
Google drive is a very powerful software allow us to synchronize all our documents and sharing in the cloud.
This tutorial is teaching on Google Spreadsheet custom trigger.

First, what is custom trigger?
Trigger is to take additional action while need to perform some additional tasks after certain condition meet.
For the example below, I am updating the Column B value to Current Time Stamp if Column A value is equal to 100.

Solution:

  1. First, create a new google spreadsheet.
  2. Setup the custom onEdit trigger in Tools > Script Editor
  3. A new function has been defined by google.
  4. Replace the new function by follow code and save it.

    function onEdit(event)
    {
      var ss = event.source.getActiveSheet();
      var r = event.source.getActiveRange();
      if(r.getColumn() == 1){ //To check if update cell in Column A, 1 means first column.
        if(r.getValue() == 100){ //To check the value is equal to 100
          ss.getRange('B'+r.getRow()).setValue(new Date()); //Set column B1 value to current date.
        }else{
          ss.getRange('B'+r.getRow()).setValue('');
        }
      }
    }
  5. Save the project.
  6. Now, we need to set the trigger for the spreadsheet. Click on the “Resources > All your triggers”
  7. And save it without any changes.
  8. Final, test out your trigger in the Google Spreadsheet. :D

Leave a Reply