Script editor overview in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst working with Google Sheets.

📋 Request: Your manager wants you to automate a task that adds a timestamp whenever a new entry is made in the sheet.

📊 Data: You have a Google Sheet with columns: Name, Task, and Timestamp. The Timestamp column is currently empty.

🎯 Deliverable: Create a simple script using the Google Sheets Script Editor that automatically fills the Timestamp column with the current date and time when a new row is added.

Sample Data

Name	Task	Timestamp
Alice	Report
Bob	Analysis
Charlie	Presentation

1

Step 1: Open the Google Sheets file and go to Extensions > Apps Script to open the Script Editor.

No formula needed; just open the Script Editor.

Expected Result

The Script Editor window opens, ready for you to write code.

2

Step 2: In the Script Editor, write a function named onEdit that runs automatically when the sheet is edited.

function onEdit(e) { var sheet = e.source.getActiveSheet(); var range = e.range; if (range.getColumn() == 2 && range.getRow() > 1) { var timestampCell = sheet.getRange(range.getRow(), 3); timestampCell.setValue(new Date()); } }

Expected Result

The script is ready to add a timestamp in column 3 when column 2 is edited.

3

Step 3: Save the script and return to the Google Sheet. Now, when you enter or change a value in the Task column (column 2), the Timestamp column (column 3) will automatically fill with the current date and time.

No formula; just test by editing the sheet.

Expected Result

When you type a task in column 2, the timestamp appears in column 3 for that row.

Final Result

Name      | Task       | Timestamp           
--------------------------------------------
Alice     | Report     | 2024-06-01 10:15 AM 
Bob       | Analysis   | 2024-06-01 10:20 AM 
Charlie   | Presentation | 2024-06-01 10:25 AM

✓The Script Editor allows you to write scripts that automate tasks in Google Sheets.

✓The onEdit function runs automatically when the sheet is changed.

✓You can use the script to add timestamps automatically, saving manual work.

Bonus Challenge

Modify the script to only add a timestamp if the Task cell is not empty and to clear the timestamp if the Task cell is cleared.

Show Hint

Use an if-else statement inside the onEdit function to check if the edited cell is empty or not, then set or clear the timestamp accordingly.