Google Sheets一键跨表复制数据及终端开票数据库AppScript开发求助

Hey there! Let's break down how to solve both of your Google Sheets automation needs clearly and step by step.

Here's a straightforward, reusable method to set this up:

• Step 1: Open the Apps Script Editor
  In your Google Sheet, go to Extensions > Apps Script to launch the script editor.

• Step 2: Write the Copy Function
  Replace the default code with this script (adjust sheet names and ranges to match your setup):

  function copyDataToSheet() {
    const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    const sourceSheet = activeSpreadsheet.getSheetByName("Sheet1"); // Replace with your source sheet name
    const targetSheet = activeSpreadsheet.getSheetByName("Sheet2"); // Replace with your target sheet name
  
    // Get all non-empty data from the source sheet
    const sourceDataRange = sourceSheet.getDataRange();
    const sourceValues = sourceDataRange.getValues();
  
    // Find the next empty row in the target sheet
    const nextEmptyRow = targetSheet.getLastRow() + 1;
  
    // Paste the data into the target sheet
    targetSheet.getRange(nextEmptyRow, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
  }
  

• Step 3: Add a Clickable Button
  Go back to your Google Sheet, click Insert > Drawing. Draw a simple button (or use a text box with "Copy Data" as the label), then save the drawing. Click the new drawing, select the three-dot menu, and choose Assign script—enter the function name copyDataToSheet and confirm. Now clicking the button will run the copy action!

For your specific use case (submit from Sheet1 to Sheet2, clear Sheet1, append on next submit), here's a tailored solution:

The Script Code #

function submitData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");

  // Get all valid data from Sheet1 (skips completely empty rows)
  const dataRange = sheet1.getDataRange();
  const dataValues = dataRange.getValues();

  // Exit if there's no data to submit
  if (dataValues.length === 0 || dataValues.every(row => row.every(cell => cell === ""))) {
    SpreadsheetApp.getUi().alert("No data to submit in Sheet1!");
    return;
  }

  // Find the next empty row in Sheet2 to append data
  const nextTargetRow = sheet2.getLastRow() + 1;

  // Write the data to Sheet2
  sheet2.getRange(nextTargetRow, 1, dataValues.length, dataValues[0].length).setValues(dataValues);

  // Clear Sheet1 (preserves header row if you have one; remove the condition below to clear everything)
  if (sheet1.getLastRow() > 1) {
    const contentRange = sheet1.getRange(2, 1, sheet1.getLastRow() - 1, sheet1.getLastColumn());
    contentRange.clearContent();
  } else {
    dataRange.clearContent();
  }

  SpreadsheetApp.getUi().alert("Data submitted successfully! Sheet1 has been cleared.");
}

Setup Steps #

1. Open the Apps Script Editor (Extensions > Apps Script) and replace the default code with the script above.
2. Save the project (click the floppy disk icon) and name it something like SubmitHandler.
3. Go back to Sheet1, insert a "SUBMIT" button via Insert > Drawing. Once you've created the button, assign the script submitData to it (same method as the first section).
4. Test it out: Enter data in Sheet1, click the button—your data will append to Sheet2, Sheet1 will clear, and you'll get a confirmation alert.

Quick Notes #

• If Sheet1 doesn't have a header row, remove the if (sheet1.getLastRow() > 1) check and just use dataRange.clearContent() to clear all cells.
• The first time you run the script, you'll need to grant necessary permissions—follow the on-screen prompts (you may need to select "Advanced > Go to [Project Name]" to proceed).

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