如何在Google Sheets中编写脚本每日21:00自动记录P/E值或指定单元格数据

Got it, let's break down exactly how to set up an automatic daily log for your P/E ratio data from the GOOGLEFINANCE function in Google Sheets. This will run every day at 21:00 and save the data to a log sheet—here's your step-by-step guide:

First, open your Google Sheet that has the P/E data. Then:

• Click Extensions in the top menu bar
• Select Apps Script from the dropdown
• This will open a new tab with the script editor, where you'll replace the default myFunction() code with our custom script.

Paste this code into the script editor, then replace the placeholder values with your sheet's details:

function logPEData() {
  // 👇 Replace these values with your own sheet info
  const spreadsheetId = "YOUR_SPREADSHEET_ID"; // Grab this from your Sheet's URL (between d/ and /edit)
  const peCell = "B2"; // The cell where your GOOGLEFINANCE P/E value lives
  const logSheetName = "PE Log"; // Name of the sheet where you want to store historical data

  // Access the spreadsheet and relevant sheets
  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sourceSheet = spreadsheet.getActiveSheet(); // Or use getSheetByName("SourceSheetName") to specify a sheet
  const logSheet = spreadsheet.getSheetByName(logSheetName);

  // Get current timestamp (adjust timezone if needed, e.g., "Asia/Shanghai")
  const timestamp = new Date();
  // Fetch the P/E value from the specified cell
  const peValue = sourceSheet.getRange(peCell).getValue();

  // Find the next empty row in the log sheet
  const nextEmptyRow = logSheet.getLastRow() + 1;

  // Write the timestamp and P/E value to the log sheet (A = timestamp, B = P/E)
  logSheet.getRange(nextEmptyRow, 1).setValue(timestamp);
  logSheet.getRange(nextEmptyRow, 2).setValue(peValue);
}

Quick Notes on the Code: #

• Make sure your log sheet (e.g., "PE Log") already exists in your spreadsheet—create it first if not.
• If your P/E value is on a specific sheet (not the active one), replace getActiveSheet() with getSheetByName("YourSheetName").
• To adjust the timezone for the timestamp, you can use Utilities.formatDate(timestamp, "Asia/Shanghai", "yyyy-MM-dd HH:mm:ss") instead of raw new Date() if you want a formatted string.

Now we need to tell Google to run this script automatically every day at 9 PM:

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Click Add Trigger in the bottom right corner.
3. Configure the trigger settings:
   • Choose which function to run: logPEData
   • Choose which deployment to run: Head
   • Select event source: Time-driven
   • Select type of time based trigger: Day timer
   • Select time of day: 9:00 PM to 10:00 PM (this will run at 21:00 in your selected timezone)
   • Select timezone: Pick your local timezone (e.g., "Asia/Shanghai")
4. Click Save. You'll need to authorize the script to access your Google Sheet—follow the prompts (Google may warn it's an "unverified app"; click "Advanced" then "Go to [Script Name]" to proceed).

Before relying on the automatic trigger, test it manually to make sure it works:

• Go back to the script editor, select logPEData from the function dropdown at the top.
• Click the run button (▶️).
• Go back to your Google Sheet and check the log sheet—you should see a new row with the current timestamp and your P/E value.

Troubleshooting Tips: #

• If the script doesn't run, double-check that all placeholder values (spreadsheet ID, cell reference, log sheet name) are correct.
• Ensure you've granted the necessary permissions when prompted.
• Verify your timezone setting in the trigger matches your local time, so it runs at 21:00 when you expect it.

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