Google Sheets Apps Script开发：实现每次运行脚本时将指定单元格值追加至新行

No problem at all! The key change you need is to find the last populated row in your GraphData sheet first, then write your new value to the row immediately after it. Here's how to modify your code:

Modified Code (Basic Version) #

var app = SpreadsheetApp; 
var tS = app.getActiveSpreadsheet().getSheetByName("Dashboard"); 
var tempNum = tS.getRange(18,11).getValue(); 
var ttS = app.getActiveSpreadsheet().getSheetByName("GraphData"); 

// Get the last row with content in the GraphData sheet
var lastRow = ttS.getLastRow();
// Calculate the new row to write to (handle empty sheet case)
var newRow = lastRow === 0 ? 1 : lastRow + 1;

// Write the value to the new row in column A
ttS.getRange(newRow, 1).setValue(tempNum);

What Changed? #

• getLastRow(): This method returns the number of the last row that has any data in the sheet. It’s perfect for pinpointing where your existing data ends.
• Empty Sheet Handling: If GraphData is completely blank, getLastRow() will return 0—the ternary operator ensures we start writing at row 1 instead of an invalid row 0.
• Dynamic Row Targeting: Instead of hardcoding A1, we use getRange(newRow, 1) to target column A of the next empty row automatically.

Bonus: Add a Timestamp (Recommended for Daily Triggers) #

Since you’re running this script daily, adding a timestamp alongside your value will make your chart data far more meaningful. Here’s the enhanced version:

var app = SpreadsheetApp; 
var tS = app.getActiveSpreadsheet().getSheetByName("Dashboard"); 
var tempNum = tS.getRange(18,11).getValue(); 
var ttS = app.getActiveSpreadsheet().getSheetByName("GraphData"); 

var lastRow = ttS.getLastRow();
var newRow = lastRow === 0 ? 1 : lastRow + 1;

// Write value to column A, timestamp to column B
ttS.getRange(newRow, 1).setValue(tempNum);
ttS.getRange(newRow, 2).setValue(new Date());

This way, you’ll have both the daily value and the date it was recorded, making it trivial to build a clear time-series chart later.

Setting Up the Daily Trigger #

Once your code is updated:

1. Open the script editor via Tools > Script editor
2. Click Edit > Current project's triggers
3. Click Add trigger
4. Configure it:
   • Select your function name from the dropdown
   • Set Event source to "Time-driven"
   • Set Type of time based trigger to "Day timer"
   • Pick the time window you want the script to run each day

That’s it—your script will now append a new row of data every day instead of overwriting the first one.

内容的提问来源于stack exchange，提问作者Jack Brady