Скачать или смотреть How to Trigger Scripts in Google Sheets on Cell Changes

Learn how to automatically run a script in Google Sheets when a specific cell is edited. Discover the step-by-step process, even for beginners!
---
This video is based on the question https://stackoverflow.com/q/68754557/ asked by the user 'Lay Lwin' ( https://stackoverflow.com/u/16648384/ ) and on the answer https://stackoverflow.com/a/68755573/ provided by the user 'arul selvan' ( https://stackoverflow.com/u/6394306/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Trigger script editor when specific cell is changed (Google sheets)

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Trigger Scripts in Google Sheets on Cell Changes

Have you ever wanted to automate tasks in Google Sheets, such as creating data snapshots every time a specific cell changes? This need arises often, especially when sharing spreadsheets where you want to track changes without manual input. A common scenario is wanting to append values from one sheet to another whenever a certain cell is modified. In this guide, we will walk through how to set up a Google Apps Script that does exactly this.

The Problem

You might have a situation where you want to capture data from one part of your spreadsheet to another automatically. For instance, you want to copy the data from cells A2 to E2 in a sheet named "Snapshot" each time the value in cell B2 is edited. However, you may encounter an issue if this cell is actually a formula linked to another cell (like F1 in a "Receipt" sheet). In such cases, the onEdit trigger won’t activate because editing a formula does not trigger the onEdit function.

The Original Attempt

Here’s a quick look at the original script:

[[See Video to Reveal this Text or Code Snippet]]

The Issue

When you tried to modify the value in cell B2 (which is a formula), this method did not activate because formulas do not trigger script executions in Google Sheets.

The Solution

To effectively solve this problem, we can modify our approach. Instead of triggering the script based on B2, we will directly monitor the cell that is responsible for its value.

Step-by-Step Breakdown

Identify the Trigger Cell: If B2 is a reflection of F1 in the "Receipt" sheet, we will set our trigger to monitor F1 instead.

Modify the Script:

We need to set up the script to append data from a specified range in the "Snapshot" sheet when F1 changes.

Here’s the revised version of the script that resolves the issue:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the New Script

Spreadsheet and Sheet Initialization: First, we grab references to the currently active spreadsheet and the specific sheets we need. This helps in organizing data correctly between sheets.

Get Values from Source Range: We get the desired values from the "Snapshot" sheet range A2 to E2.

Check for Edit: Instead of B2, we now check if the edited cell is F1. If it is, we append the previously fetched values to the next available row in the "Snapshot" sheet.

Conclusion

Using Google Apps Script efficiently enables you to automate tasks within your Google Sheets without needing extensive coding knowledge. By knowing how to set triggers, you can significantly enhance the functionality and usefulness of your spreadsheets.

With these simple steps, you can easily track and create data snapshots in response to changes, which can save time and reduce errors in your data management processes.

Are you ready to automate your Google Sheets? Give it a try and enjoy the benefits of improved workflow!