同步 Excel 和 GoogleSheet

尝试替换以下行：

targetSheet.setName(sheetName);

跟：

try { targetSheet.setName(sheetName) } catch(e) {}

如果电子表格已具有给定名称，则不会重命名工作表。

或者，您可以尝试获取具有相同名称的工作表并重命名它。像这样的东西：

try {
  var temp_sheet = newSpreadsheet.getSheetByName(sheetName);
  temp_sheet.setName(temp_sheet.getName() + "#");
} catch(e) {}

更新

在添加新工作表之前，您可以尝试删除带有名称的工作表：sheetName

try {
  var sheet_to_remove = newSpreadsheet.getSheetByName(sheetName);
  newSpreadsheet.deleteSheet(sheet_to_remove);
} catch(e) {}

注意：如果电子表格中只有一个工作表，它可能不会删除工作表。如果是你的情况，可能将这两种方法结合起来是有意义的：

• 首先尝试获取同名的工作表并重命名它
• 复制新工作表
• 尝试删除重命名的工作表

像这样的东西：

var sheet_to_delete = newSpreadsheet.getSheetByName(sheetName);
if (sheet_to_delete != null) sheet_to_delete.setName(sheetName + "#");

// here is the part of your code to copy the data from your .xls

if (sheet_to_delete != null) newSpreadsheet.deleteSheet(sheet_to_delete);

更新 2

尝试替换以下行：

for (var i = 0; i < sheets.length; i++) {
  var newSheet = sheets[i];
  var data = newSheet.getDataRange().getValues();
  var sheetName = newSheet.getName();
  var targetSheet = sheet.copyTo(SpreadsheetApp.getActiveSpreadsheet());
  targetSheet.setName(sheetName);
  targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}

跟：

var ss = SpreadsheetApp.getActiveSpreadsheet(); // the target spreadsheet

for (var i = 0; i < sheets.length; i++) {

  // get the name of the copied sheet from the source spreadsheet
  var sheetName = sheets[i].getName();

  // copy the sheet from the source spreadsheet to the target one
  // it copies the sheet a whole with its formatting, not just data from the cells
  // the copy `targetSheet` gets some default name 'Copy of ...'
  var targetSheet = sheets[i].copyTo(ss);

  // on the target spreadsheet try to get the old sheet with the name `sheetName`
  var old_sheet = ss.getSheetByName(sheetName);    

  // if there is the sheet, delete it
  if (old_sheet != null) ss.deleteSheet(old_sheet);

  // assign to the copied sheet the proper name instead of the default one
  targetSheet.setName(sheetName);
}

如果你想删除临时（？）文件，在循环之后添加以下行是有意义的：[Google Sheets] Mijn Bestand.xlsm

Drive.Files.trash(newFile.getId());

它会自动将文件放入垃圾箱。