How to Create an Automatic Timestamp in Google Sheets with Apps Script

Описание: Learn how to automatically timestamp changes in multiple tabs of your Google Sheets using Google Apps Script, with detailed steps and code improvements.
---
This video is based on the question https://stackoverflow.com/q/77174475/ asked by the user 'marybr555' ( https://stackoverflow.com/u/22632205/ ) and on the answer https://stackoverflow.com/a/77174537/ provided by the user 'brandonscript' ( https://stackoverflow.com/u/1214800/ ) 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: Apps Script Automatic timestamp when a tab gets updated

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 Create an Automatic Timestamp in Google Sheets with Apps Script

If you're working with Google Sheets and want to track when changes are made to certain tabs, you're in the right place. Keeping a timestamp for when each tab is updated can improve your data management and accountability. In this guide, we’ll walk through a common problem: creating a script that automatically updates a timestamp when a cell is modified in your spreadsheet tabs.

The Problem: How to Monitor Changes in Your Spreadsheet Tabs

You have a Google Sheet with multiple tabs, and you want to automatically update the timestamp in specific cells every time there's an edit in any of these tabs. You’ve attempted to write a script but noticed it stopped working after a couple of days without any changes to the script itself. Here’s the original code you were using:

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

Understanding the Issue

There are multiple potential reasons why the script may have stopped functioning. Most notably, the issue could stem from how the tab names are referenced. The Tabs in Google Sheets do not contain spaces by default; hence Sheet 1, Sheet 2, etc. should actually be Sheet1, Sheet2, etc.

The Solution: Code Improvements for Automatic Timestamps

Let’s walk through the corrected code that not only fixes the problems but also improves your original script.

1. Correct the Tab Names

Start by ensuring you are using the correct names of the tabs:

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

2. Simplify the Logic with Switch Case

Instead of repeating the same logic multiple times, we can make it cleaner by using a switch/case statement:

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

3. Key Improvements Explained

Remove Unnecessary Calls: The e.source.getSheetByName("Sheet X") calls were not needed, as you're already working with the active sheet.

Use Constants: The date variable is defined just once outside the conditions, making it more efficient.

Modern JavaScript Practices: Using const (or let for mutable variables) instead of var improves readability and minimizes potential issues in variable scope.

Conclusion

By following these revisions, your script will maintain accurate timestamps across different tabs, allowing you to effortlessly track changes over time. Debugging and improving your code might seem daunting initially, but with some practice, you will become adept at scripting in Google Apps Script for Google Sheets.

Now, give your updated script a try, and enjoy the ease of monitoring your data edits! Happy coding!