请求编写Google Sheets脚本：批量遍历股票代码并同步数据至指定工作表

Hey there! Let's get this stock data automation sorted out for you. Since you're using the Google Finance API, I'll cover solutions for both Google Sheets (using Apps Script) and Excel (using VBA) to cover all bases.

Google Sheets Solution (Apps Script) #

This script will automate the entire workflow: pull tickers from the tickers sheet, feed them one by one into data!B9, wait for the API to load results, then copy those results to the results sheet.

Step-by-Step Setup: #

1. Open your Google Sheet
2. Click Extensions > Apps Script to open the script editor
3. Replace any existing code with the script below
4. Save the project (name it something like "StockDataFetcher")
5. Run the script (you'll need to grant permissions the first time it runs)

The Script: #

function fetchStockData() {
  // Grab the active spreadsheet and your three sheets
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const tickersSheet = ss.getSheetByName('tickers');
  const dataSheet = ss.getSheetByName('data');
  const resultsSheet = ss.getSheetByName('results');

  // Optional: Clear existing results to start fresh
  const lastResultRow = resultsSheet.getLastRow();
  if (lastResultRow >= 2) {
    resultsSheet.getRange(2, 1, lastResultRow - 1, 46).clearContent(); // F to AU is 46 columns
  }

  // Get all tickers from B2:B40, filter out empty cells
  const tickers = tickersSheet.getRange('B2:B40').getValues()
    .filter(row => row[0] !== '');

  // Loop through each ticker
  tickers.forEach((tickerRow, index) => {
    const ticker = tickerRow[0];
    if (!ticker) return;

    // Write the ticker to data!B9
    dataSheet.getRange('B9').setValue(ticker);

    // Wait for the Google Finance API to load data
    // Option 1: Fixed 30-second wait (matches your noted load time)
    Utilities.sleep(30000);

    // Option 2: Smarter wait (check if F2 has valid data, avoids unnecessary waiting)
    // Uncomment this block and comment the sleep above if you prefer
    /*
    let waitSeconds = 0;
    const maxWait = 60; // Max wait 60 seconds
    while (true) {
      const f2Value = dataSheet.getRange('F2').getValue();
      if (f2Value !== '' && !dataSheet.getRange('F2').isError()) break;
      if (waitSeconds >= maxWait) {
        SpreadsheetApp.getUi().alert(`Timeout loading data for ${ticker}`);
        break;
      }
      Utilities.sleep(1000);
      waitSeconds++;
    }
    */

    // Copy the results from F2:AU2 to the results sheet
    const resultData = dataSheet.getRange('F2:AU2').getValues();
    resultsSheet.getRange(2 + index, 1, 1, resultData[0].length).setValues(resultData);
  });

  // Let you know when it's done
  SpreadsheetApp.getUi().alert('All stock data has been fetched successfully!');
}

Key Notes: #

• The fixed 30-second wait is simple but rigid. The smarter wait option checks if data!F2 has valid data before proceeding, which is better if API load times vary.
• We filter out empty cells in the tickers range so the script skips any blank entries in B2:B40.

Excel Solution (VBA) #

If you're using Excel instead of Google Sheets, this VBA macro will do the same job.

Step-by-Step Setup: #

1. Open your Excel workbook
2. Press Alt + F11 to open the VBA Editor
3. Right-click your workbook in the Project Explorer > Insert > Module
4. Paste the macro below into the module
5. Press F5 to run the macro, or assign it to a button for easier access

The Macro: #

Sub FetchStockData()
    Dim wsTickers As Worksheet, wsData As Worksheet, wsResults As Worksheet
    Dim tickerCell As Range
    Dim lastResultRow As Long
    Const WAIT_SECONDS As Integer = 30 ' Match your API load time

    ' Assign your worksheets
    Set wsTickers = ThisWorkbook.Sheets("tickers")
    Set wsData = ThisWorkbook.Sheets("data")
    Set wsResults = ThisWorkbook.Sheets("results")

    ' Optional: Clear existing results
    If wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row >= 2 Then
        wsResults.Range("A2:AU" & wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row).ClearContents
    End If

    ' Loop through each ticker in B2:B40
    For Each tickerCell In wsTickers.Range("B2:B40")
        If tickerCell.Value <> "" Then
            ' Write ticker to data!B9
            wsData.Range("B9").Value = tickerCell.Value

            ' Wait for the API to load data
            Application.Wait Now + TimeValue("00:00:" & WAIT_SECONDS)

            ' Copy results to the next empty row in results sheet
            lastResultRow = wsResults.Cells(wsResults.Rows.Count, "A").End(xlUp).Row + 1
            wsData.Range("F2:AU2").Copy Destination:=wsResults.Range("A" & lastResultRow)
        End If
    Next tickerCell

    MsgBox "Stock data fetching complete!", vbInformation
End Sub

Key Notes: #

• The WAIT_SECONDS constant is set to 30, adjust it if your API sometimes takes longer to load.
• If you want a smarter wait (like checking for valid data in data!F2), you can replace the Application.Wait line with a loop that checks the cell value until it's not empty/error.

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