如何修改代码实现通过Gmail附件向Google Sheet追加数据而非覆盖原有内容

Hey there! I see you’re trying to add data from Gmail attachments to your Google Sheet without wiping out existing rows—let’s adjust your script to do exactly that. The main issue with your current code is the clearContents() and clear() calls that erase your existing data; we’ll replace those with logic to find the next empty row and append the new data there.

Modified Code #

function myFunction() {
  var thread = GmailApp.getUserLabelByName("mkto").getThreads(0,1);
  var messages = thread[0].getMessages();
  var len = messages.length;
  var message = messages[len-1]; // Get the latest message in the thread
  var attachments = message.getAttachments(); // Pull attachments from the message
  var blob = attachments[0]; // Assume the first attachment is your target file
  blob.setContentTypeFromExtension();

  if (blob.getContentType() == MimeType.MICROSOFT_EXCEL) {
    // Handle XLSX file conversion and data extraction
    var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, blob).id;
    var tempSheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];
    var data = tempSheet.getDataRange().getValues();
    Drive.Files.remove(convertedSpreadsheetId); // Clean up the temporary converted file

    // Target your existing sheet
    var targetSheet = SpreadsheetApp.openById("1Bmcl6p42rBHeIswaSWV861bCrlhZlAJtHPqwJYsnClc").getSheetByName("data");
    var lastRow = targetSheet.getLastRow(); // Find the last row with existing data
    // Append new data starting at the next empty row
    var appendRange = targetSheet.getRange(lastRow + 1, 1, data.length, data[0].length);
    appendRange.setValues(data);
  } else if (blob.getContentType() == MimeType.CSV) {
    // Handle CSV data parsing
    var csv = blob.getDataAsString();
    var data = Utilities.parseCsv(csv);

    // Target your existing sheet
    var targetSheet = SpreadsheetApp.openById("1Bmcl6p42rBHeIswaSWV861bCrlhZlAJtHPqwJYsnClc").getSheetByName("data");
    var lastRow = targetSheet.getLastRow(); // Find the last row with existing data
    // Append CSV data starting at the next empty row
    var appendRange = targetSheet.getRange(lastRow + 1, 1, data.length, data[0].length);
    appendRange.setValues(data);
  }
}

Key Changes Explained #

• Removed clearContents() and clear() calls: These were wiping your existing data—we don’t need them anymore.
• Added var lastRow = targetSheet.getLastRow();: This finds the last row that contains data in your target sheet, so we know where to start appending.
• Updated the range to start at lastRow + 1: This ensures new data is added to the first empty row instead of overwriting the top of the sheet.

Optional: Skip Attachment Headers (If Needed) #

If your attachment includes a header row that’s already present in your target sheet, you can skip appending it by modifying the data variable like this:

// For XLSX files (skip first row):
var data = tempSheet.getDataRange().getValues().slice(1);

// For CSV files (skip first row):
var data = Utilities.parseCsv(csv).slice(1);

Quick Notes to Avoid Issues #

• Make sure the number of columns in your attachment data matches the columns in your target sheet—otherwise, you’ll get a range mismatch error.
• If you sometimes get multiple attachments in threads, you might want to add a check for the filename (using blob.getName()) to ensure you’re processing the correct file.

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