将一张记录从一个电子表格转到另一个Google D docs的电子表格(应用说明)中。

function myFunction() {
    // Get Spreadsheets
    var source = SpreadsheetApp.openById("spreadsheetKeySource");
    var target = SpreadsheetApp.openById("spreadsheetKeyTarget");

    // Set Sheets
    var source_sheet = source.getSheetByName("Sheet1");
    var target_sheet = target.getSheetByName("Sheet1");

    // Get target last row
    var last_row = target_sheet.getLastRow();

    // Set Ranges
    var source_range = source_sheet.getRange("A1:B1");
    var target_range = target_sheet.getRange("A"+(last_row+1)+":B"+(last_row+1));

    // Fetch values
    var values = source_range.getValues();

    // Save to spreadsheet
    target_range.setValues(values);
}

I need to know if it is possible to fetch the content from the source and loop through it and only add records that do not exist in the target. Obviously, this function is inadequate for this purpose and I am hoping someone can push me in the right direction or even provide a code snippet as an example. I cannot find anything useful on Google or here so far... :(

谢谢你的帮忙

<强 > EDIT

我完成了这个任务。 范围改变了, 现在我不得不从多张工作表的多个电子表格中获取数据, 只取电子邮件并将其添加到主电子邮件电子表格中。 这是我创建的代码, 希望它也能帮助别人 。

当 masterSpreadshist 被打开并添加菜单按钮来运行此脚本时, 我实际上运行了此脚本 。

/**
* Updates the master spreadsheet with the set source spreadsheets and pages
**/
function updateMasterSpreadsheet() {
  //  var sourceSpreadsheetsArray = new Array();
  var sourceSpreadsheetsObject = new Object;

  // Add Spreadsheet IDs and sheet names that need to be copied from
  sourceSpreadsheetsObject[ sourceSpreadsheet1Key ] = new Array("Sheet1");
  sourceSpreadsheetsObject[ sourceSpreadsheet2Key ] = new Array("Sheet1");

  // Open master spreadsheet
  var target = SpreadsheetApp.openById("masterSpreadsheetKey");

  // Open master sheet
  var target_sheet = target.getSheetByName("Sheet1");

  // Loop through all source spreadsheets
  for (var id in sourceSpreadsheetsObject) {
    // Open source spreadsheet
    var source = SpreadsheetApp.openById(id);

    // Loop through and process each sheet
    for (var sheetID in sourceSpreadsheetsObject[id]) {
      // Open source spreadsheet sheet
      var source_sheet = source.getSheetByName(sourceSpreadsheetsObject[id][sheetID]);

      // Process sheet
      _updateMasterSpreadsheet(source_sheet, target_sheet);
    } // END - source sheets loop 

  } // END - source spreadsheets loop

  // Add last updated
  target_sheet.getRange("B1").setValue(new Date());
}

/**
* Performs the actual copy into the master spreadsheet
**/
function _updateMasterSpreadsheet(source_sheet, target_sheet) {
  // Get target last row
  var last_row = target_sheet.getLastRow();  

  // Get Source Range
  var source_range = source_sheet.getDataRange();

  // Fetch Source Values
  var source_data = source_range.getValues();  

  //Iterate over all cells, looking for non-empty ("") cells
  for (var row in source_data) {

    // check if empty
    if (source_data[row][0]!= "") {
      // Fetch data in the sheet (do this in the loop to ensure that we check all newly added items)
      var target_range = target_sheet.getDataRange();
      var target_data = target_range.getValues();  

      // Set flags
      var found = false;
      var stop = false;

      // Process loop
      while(found == false && stop == false) {
        // Check for duplicates
        for (var tmpRow in target_data) {
          if (source_data[row][0] == target_data[tmpRow][0]) {
            found = true;
            break;
          }
        }

        // If no duplicate, add to sheet
        if (!found) {
          last_row++;
          target_sheet.getRange("A"+last_row).setValue(source_data[row][0]);
        }

        // Prevent infinite loop
        stop = true;
      }
    }
  }
}