Hey there! Let's fix up your script to add those two features you need. I'll walk you through the key changes first, then share the full working code that's beginner-friendly.

Key Changes Explained #

1. Data Extraction to PO Log Sheet #

• First, we find the last used row in the target "PO Log" sheet's column E to make sure we append new data without overwriting existing entries.
• Extract values from cells F5, G5, A11, A22 and write them to columns A-D of the next empty row.
• Loop through the C25:C46 range to collect only non-empty values, then write each one to column E in consecutive rows (so if there are 3 non-empty rows in C25:C46, they'll go to E[last_row+1], E[last_row+2], E[last_row+3]).

2. Email Validation & Warning Prompt #

• Before sending the email, we check if cell A16 is empty or doesn't match a basic email format (using a simple regex for quick validation).
• If it's invalid/empty, we show a dialog with two options: "Return to Fill Email" (stops the script so you can edit the cell) or "Skip Sending Email" (continues generating the PDF but skips the mail step).

Full Updated Script #

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Create PDF')
    .addItem('Generate PDF & Log Data', 'createpdf')
    .addToUi();
}

function createpdf() {
  const sourceSpreadsheet = SpreadsheetApp.getActive();
  const sourceSheet = sourceSpreadsheet.getSheetByName("Sales Order Form");
  
  // --------------------------
  // Step 1: Extract & Log Data to PO Log Sheet
  // --------------------------
  // Replace YOUR_PO_LOG_SPREADSHEET_ID with your actual sheet ID
  const poLogSheet = SpreadsheetApp.openById('YOUR_PO_LOG_SPREADSHEET_ID').getSheetByName("PO Log");
  // Get last used row in column E to append new data
  const lastUsedRow = poLogSheet.getRange("E:E").getValues().filter(String).length;
  const nextRow = lastUsedRow + 1;

  // Extract individual cell values
  const f5Value = sourceSheet.getRange("F5").getValue();
  const g5Value = sourceSheet.getRange("G5").getValue();
  const a11Value = sourceSheet.getRange("A11").getValue();
  const a22Value = sourceSheet.getRange("A22").getValue();

  // Write individual values to columns A-D of next empty row
  poLogSheet.getRange(`A${nextRow}`).setValue(f5Value);
  poLogSheet.getRange(`B${nextRow}`).setValue(g5Value);
  poLogSheet.getRange(`C${nextRow}`).setValue(a11Value);
  poLogSheet.getRange(`D${nextRow}`).setValue(a22Value);

  // Extract non-empty values from C25:C46 and write to column E
  const cRangeValues = sourceSheet.getRange("C25:C46").getValues();
  let currentErow = nextRow;
  for (let i = 0; i < cRangeValues.length; i++) {
    const cellValue = cRangeValues[i][0];
    if (cellValue) { // Only write non-empty values
      poLogSheet.getRange(`E${currentErow}`).setValue(cellValue);
      currentErow++;
    }
  }

  // --------------------------
  // Step 2: Email Validation Check
  // --------------------------
  const email = sourceSheet.getRange("A16").getValue().trim();
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  let sendEmail = true;

  if (!email || !emailRegex.test(email)) {
    const ui = SpreadsheetApp.getUi();
    const response = ui.alert(
      "Missing or Invalid Email",
      "Cell A16 doesn't contain a valid email address. Do you want to return to fill it in, or skip sending the email?",
      ui.ButtonSet.YES_NO
    );

    if (response === ui.Button.YES) {
      // User chose to return to edit email - stop the script
      ui.alert("Script paused. Please fill in A16 and run the script again.");
      return;
    } else {
      // User chose to skip sending email
      sendEmail = false;
      ui.alert("Email sending will be skipped. Continuing to generate PDF...");
    }
  }

  // --------------------------
  // Existing PDF Generation Logic (Cleaned Up)
  // --------------------------
  const sheetName = sourceSheet.getName();
  const pdfName = sourceSheet.getRange("A11").getValue() || "Sales Order";
  
  // Get parent folder of the source spreadsheet
  const parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  const folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();

  // Create temporary spreadsheet for PDF conversion
  const destSpreadsheet = sourceSpreadsheet.copy("tmp_convert_to_pdf", folder);
  
  // Delete all sheets except the sales order form
  const sheets = destSpreadsheet.getSheets();
  for (const sheet of sheets) {
    if (sheet.getSheetName() !== sheetName) {
      destSpreadsheet.deleteSheet(sheet);
    }
  }

  // Copy values (not formulas) to temporary sheet to avoid broken references
  const sourceRange = sourceSheet.getDataRange();
  const sourceValues = sourceRange.getValues();
  const destRange = destSpreadsheet.getSheetByName(sheetName).getRange(1, 1, sourceValues.length, sourceValues[0].length);
  destRange.setValues(sourceValues);

  // Generate PDF file
  const pdfBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  const newFile = folder.createFile(pdfBlob);

  // Send email only if validation passed or user chose to skip
  if (sendEmail) {
    const subject = sourceSheet.getRange("F2").getValue() || "Your Sales Order";
    const body = `\nThank you for your purchase/inquiry. Attached is your Sales Order/Invoice.\n\nThank You\nYour Company Name`;
    MailApp.sendEmail(email, subject, body, {
      attachments: [pdfBlob],
      name: 'Your Company Sales Team'
    });
  }

  // Clean up temporary spreadsheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

  // Clear form fields (as in original script)
  sourceSheet.getRange('A11:E11').clearContent();
  sourceSheet.getRange('H47').clearContent();
  sourceSheet.getRange('A22:H22').clearContent();
  sourceSheet.getRange('C25:D46').clearContent();
  sourceSheet.getRange('B18:G19').clearContent();
  sourceSheet.getRange('G25:G46').clearContent();

  // Update tally counter - replace YOUR_TALLY_SPREADSHEET_ID with your actual ID
  const tallySheet = SpreadsheetApp.openById("YOUR_TALLY_SPREADSHEET_ID").getSheetByName("SO tally counter");
  const currentTally = tallySheet.getRange('A2').getValue();
  tallySheet.getRange('A2').setValue(currentTally + 1);
}

Quick Setup Tips #

• Replace YOUR_PO_LOG_SPREADSHEET_ID and YOUR_TALLY_SPREADSHEET_ID with the actual IDs from your spreadsheet URLs (the long string between /d/ and /edit).
• The email regex checks for a basic user@domain.tld format—if you need stricter validation, you can tweak the regex, but this works for most everyday cases.
• We added trim() to the email input to ignore accidental extra spaces that might cause validation failures.

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