如何在基于Google Apps Script导入Gmail内容至电子表格时指定各数据项的目标列位置？

Got it, let's tweak your script so you can explicitly assign each ex0, ex1, and ex2 value to specific columns in your spreadsheet. No more sticking to the default A-B-C order—you get to pick exactly where each data point goes.

Here's the Modified Script #

First, we'll add a column mapping object to define which column each data item belongs to. Then adjust how we build the row data to populate those specific columns:

function importGmailToSheet() { 
  var threads = GmailApp.search('label:extra is:unread'); 
  var messages = GmailApp.getMessagesForThreads(threads); 
  var sheet = SpreadsheetApp.getActive().getSheetByName('2021'); 
  
  // 👇 Define your custom column mapping here (column numbers start at 1)
  const COLUMN_MAPPING = {
    ex0: 3,  // ex0 values go to column C (3rd column)
    ex1: 5,  // ex1 values go to column E (5th column)
    ex2: 7   // ex2 values go to column G (7th column)
  };
  
  // Get the maximum column number from our mapping to size our rows correctly
  const MAX_COLUMN = Math.max(...Object.values(COLUMN_MAPPING));

  for(var i=0; i<messages.length; i++) { 
    var plainBody = messages[i][0].getPlainBody(); 
    var lastRow = sheet.getLastRow(); 
    const regex=/ex0：(.*)\n(.*)ex1：(.*)\n(.*)ex2：(.*)\n/g; 
    
    if(!plainBody.match(regex)) { 
      continue; 
    } 

    var data = plainBody.match(regex).map(x=> { 
      // Extract values as before
      var a0 = (/ex0：(.*)/).test(x)? RegExp.$1 : ''; 
      var a1 = (/ex1：(.*)/).test(x)? RegExp.$1 : ''; 
      var a2 = (/ex2：(.*)/).test(x)? RegExp.$1 : ''; 

      // Create an empty row array (fill with empty strings for unused columns)
      const row = new Array(MAX_COLUMN).fill(''); 
      
      // Assign values to their specified columns (array index = column number - 1)
      if (a0) row[COLUMN_MAPPING.ex0 - 1] = a0;
      if (a1) row[COLUMN_MAPPING.ex1 - 1] = a1;
      if (a2) row[COLUMN_MAPPING.ex2 - 1] = a2;

      return row; 
    }); 

    // Write the data to the sheet—note we use MAX_COLUMN for the number of columns
    sheet.getRange(lastRow+1, 1, data.length, MAX_COLUMN).setValues(data); 
  } 

  threads.forEach(function(thread) { 
    thread.markRead(); 
    Utilities.sleep(100); 
  }); 
}

Key Changes Explained #

• COLUMN_MAPPING Object: This is where you set the rules. Just change the numbers to match your target columns (remember, Sheets columns start at 1—so column A is 1, B is 2, etc.).
• Empty Row Initialization: We create a row array sized to your maximum target column, filled with empty strings. This ensures unused columns stay blank instead of shifting data around.
• Column Index Adjustment: Since JavaScript arrays use 0-based indexing but Sheets uses 1-based column numbers, we subtract 1 when assigning values to the row array.
• Dynamic Column Range: Using MAX_COLUMN ensures we always write to the correct number of columns, even if you update the mapping later.

Quick Tips #

• If you add more data items (like ex3), just add a new entry to COLUMN_MAPPING and extend the value extraction logic.
• Replace the empty string '' in fill('') with a default value (like '0') if you prefer placeholder text for missing data.
• Test with a single unread email first to make sure the column mapping works as expected before processing a batch.

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