请求开发Google Sheets AppScript：自动导入昨日数据至Sheet3

Script Option 1: Sheet2 contains only yesterday's data (no hidden rows) #

Use this if Sheet2 is set up to only display yesterday's data (e.g., via a query formula that outputs only relevant rows, not a filter view hiding rows):

function copyYesterdaysDataToSheet3() {
  // Access the active spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get references to Sheet2 and Sheet3
  const sheet2 = ss.getSheetByName('Sheet2');
  const sheet3 = ss.getSheetByName('Sheet3');
  
  // Get all data from Sheet2
  const dataRange = sheet2.getDataRange();
  const allValues = dataRange.getValues();
  
  // Remove the header row (delete this line if Sheet2 has no header)
  const dataToCopy = allValues.slice(1);
  
  // Append data to Sheet3 if there's content to copy
  if (dataToCopy.length > 0) {
    const pasteStartRow = sheet3.getLastRow() + 1;
    sheet3.getRange(pasteStartRow, 1, dataToCopy.length, dataToCopy[0].length).setValues(dataToCopy);
    SpreadsheetApp.getUi().alert(`Copied ${dataToCopy.length} rows to Sheet3 successfully!`);
  } else {
    SpreadsheetApp.getUi().alert('No data found in Sheet2 to copy.');
  }
}

Script Option 2: Sheet2 uses a filter view to hide non-yesterday rows #

If Sheet2 has a filter applied (e.g., A column equals =TODAY()-1) that hides other rows, use this script to only copy visible rows:

function copyYesterdaysDataToSheet3() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet2 = ss.getSheetByName('Sheet2');
  const sheet3 = ss.getSheetByName('Sheet3');
  
  // Get all rows from Sheet2 (including hidden ones)
  const allRows = sheet2.getDataRange().getValues();
  const visibleRows = [];
  
  // Loop through rows starting after the header (adjust i=1 to i=0 if no header)
  for (let i = 1; i < allRows.length; i++) {
    // Check if the row is visible (not hidden by filter)
    if (!sheet2.isRowHiddenByFilter(i + 1)) { // Sheets uses 1-indexed row numbers
      visibleRows.push(allRows[i]);
    }
  }
  
  // Append visible rows to Sheet3
  if (visibleRows.length > 0) {
    const pasteStartRow = sheet3.getLastRow() + 1;
    sheet3.getRange(pasteStartRow, 1, visibleRows.length, visibleRows[0].length).setValues(visibleRows);
    SpreadsheetApp.getUi().alert(`Copied ${visibleRows.length} visible rows to Sheet3!`);
  } else {
    SpreadsheetApp.getUi().alert('No visible data found in Sheet2.');
  }
}

How to Use the Script #

1. Open your Google Sheet.
2. Click Extensions > Apps Script to open the script editor.
3. Delete any existing code and paste one of the scripts above.
4. Save the project (click the floppy disk icon) and name it something like "CopyYesterdayData".
5. Close the script editor.

Add a Button to Run the Script #

1. Go back to your sheet, click Insert > Drawing.
2. Create a button (e.g., use the text box tool to write "Copy Yesterday's Data", add a border if desired).
3. Click Save and Close to place the drawing on your sheet.
4. Click the drawing, then click the three dots in the top-right corner > Assign script.
5. Type the function name copyYesterdaysDataToSheet3 and click OK.
6. Now you can click the button anytime to run the script.

Key Notes for Beginners #

• Header Row Adjustment: If Sheet2 doesn't have a header row, change allValues.slice(1) to allValues in Option 1, or start the loop at i=0 in Option 2.
• Permissions: The first time you run the script, you'll need to grant permissions (follow prompts, click "Advanced" and "Go to [Project Name]" to allow access).
• Error Handling: Alert messages will notify you if there's no data to copy, simplifying troubleshooting.

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