Detect user inserting row or column in a google spreadsheet and reacting in a script

by

Google added an “On Change” event that detects row/column insertion/deletion along with other types of changes, the types for which you can see here under the allowed values for changeType. Following are the instructions from here detailing how to add a trigger to your project, so that you may have your function called when the “On Change” event occurs.

To manually create an installable trigger through a dialog in the script editor, follow these steps:

  1. From the script editor, choose Edit > Current project’s triggers.
  2. Click the link that says: No triggers set up. Click here to add one now.
  3. Under Run, select the name of function you want to trigger.
  4. Under Events, select either Time-driven or the Google App that the script is bound to (for example, From spreadsheet).
  5. Select and configure the type of trigger you want to create (for example, an Hour timer that runs Every hour or an On open trigger).
  6. Optionally, click Notifications to configure how and when you will be contacted by email if your triggered function fails.
  7. Click Save.

In step 4 you would select From spreadsheet and in step 5 you would select On change. That should have the effect you’re looking for. There are also options for adding triggers programmatically and requesting authorization, if you are trying to use this in an add-on to be distributed to users. Both are detailed in the Installable Triggers documentation.

More Related Contents:

  1. You do not have permission to call openById
  2. Custom Functions with add-ons?
  3. Use script in all spreadsheets
  4. We’re sorry, a server error occurred while reading from storage. Error code PERMISSION_DENIED
  5. Publish an add-on privately
  6. Refresh data retrieved by a custom function in Google Sheet
  7. Create Google Calendar Events from Spreadsheet but prevent duplicates
  8. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  9. Apps Script: how to get hyperlink from a cell where there is no formula
  10. Retrieving Google Sheets cell border style programmatically
  11. Determining the last row in a single column
  12. Cannot find method getRange(number,number)?
  13. Google Apps Script, copy one spreadsheet to another spreadsheet with formatting
  14. Adding Buttons To Google Sheets and Set value to Cells on clicking
  15. How do I communicate a page(modal dialog) with its sibling (sidebar)?
  16. How to replace text in Google Spreadsheet using App Scripts?
  17. Custom function throws a “You do not have the permission required to setValue” error
  18. Margins parameters for Google spreadsheet export as PDF
  19. Google Apps Script: Dynamically creating spreadsheet menu items
  20. Script to convert .XLSX to Google Sheet and move converted file
  21. Hidden field in a Google Form
  22. Apply multiple font colors to the text in a single Google Sheets cell
  23. Google Sheet use Importxml error could not fetch url [duplicate]
  24. On Edit doesnt work if cell auto updates – Need a workaround Google Sheets
  25. Iterate over range, append string to each
  26. Can the google spreadsheet ‘query’ function be used in google apps script?
  27. How to pause App Scripts until spreadsheet finishes calculation
  28. Custom Functions and Recalculation
  29. How to create INDIRECT array string of multiple sheet references in Google Sheets?
  30. Can the google spreadsheet ‘query’ function be used in google apps script?

Leave a Comment