如何将现有的Google工作表插入到Google电子表格中?

在尝试了几种不同的方法之后，最可靠的方法被证明是Google Apps Scripting。一般来说，我的解决方案涉及一个Google Apps脚本，该脚本由我的c#应用程序通过执行API调用。下面是一些代码示例，演示所有这些是如何协同工作的。

这是Google Apps的脚本，它将内容从一个工作表移动到另一个工作表:

function copyWorksheet( destinationSpreadsheetId, destinationWorksheetTitle, originSpreadsheetId, originWorksheetTitle ) {
  // Spreadsheet where new data will go:
  var dss = SpreadsheetApp.openById(destinationSpreadsheetId);
  // Spreadsheet where new data is coming from:
  var oss = SpreadsheetApp.openById(originSpreadsheetId);
  // Worksheet containing new data:
  var dataOriginWorksheet = oss.getSheetByName(originWorksheetTitle);
  // Worksheet whose data will be 'overwritten':
  var expiredWorksheet = dss.getSheetByName(destinationWorksheetTitle);
  // If a spreadsheet only has one worksheet, deleting that worksheet causes an error.
  // Thus we need to know whether the expired worksheet is the only worksheet in it's parent spreadsheet.
  var expiredWorksheetIsAlone = dss.getNumSheets() == 1 && expiredWorksheet != null;
  // Delete the expired worksheet if there are other worksheets:
  if (expiredWorksheet != null && !expiredWorksheetIsAlone)
    dss.deleteSheet(expiredWorksheet);
  // Otherwise, rename it to something guaranteed not to clash with the new sheet's title:
  if(expiredWorksheetIsAlone)
    expiredWorksheet.setName(dataOriginWorksheet.getName() + destinationWorksheetTitle);
  // Copy the new data into it's rightful place, and give it it's rightful name.
  dataOriginWorksheet.copyTo(dss).setName(destinationWorksheetTitle);
  // Since there are now definitely 2 worksheets, it's safe to delete the expired one.
  if(expiredWorksheetIsAlone)
    dss.deleteSheet(expiredWorksheet);
  // Make sure our changes are applied ASAP:
  SpreadsheetApp.flush();
  return "finished";
}

这是我最终使用的代码的严格精简版本，这就是为什么有两个电子表格ID字段。这意味着这两个工作表是否在同一个电子表格中并不重要。

解决方案的c#部分看起来像这样:

// We need these for the method below
using Google.Apis.Script.v1;
using Google.Apis.Script.v1.Data;
...    
public static bool copyWorksheet(ScriptService scriptService, string destinationSpreadsheetId, string destinationWorksheetTitle, string originSpreadsheetId, string originWorksheetTitle)
  {
    // You can get the script ID by going to the script in the 
    // Google Apps Scripts Editor > Publish > Deploy as API executable... > API ID
    string scriptId = "your-apps-script-id";
    ExecutionRequest request = new ExecutionRequest();
    request.Function = "copyWorksheet";
    IList<object> parameters = new List<object>();
    parameters.Add(destinationSpreadsheetId);
    parameters.Add(destinationWorksheetTitle);
    parameters.Add(originSpreadsheetId);
    parameters.Add(originWorksheetTitle);            
    request.Parameters = parameters;
    ScriptsResource.RunRequest runReq = scriptService.Scripts.Run(request, scriptId);
    try
    {
      Operation op = runReq.Execute();
      if (op.Error != null)
      {
        Console.WriteLine(DateTime.Now.ToString("HH:mm:ss") + " The Apps script encountered an error");
        // The API executed, but the script returned an error.
        IDictionary<string, object> error = op.Error.Details[0];
        Console.WriteLine( "Script error message: {0}", error["errorMessage"]);
        if ( error.ContainsKey("scriptStackTraceElements") )
        {
          // There may not be a stacktrace if the script didn't
          // start executing.
          Console.WriteLine("Script error stacktrace:");
          Newtonsoft.Json.Linq.JArray st = (Newtonsoft.Json.Linq.JArray)error["scriptStackTraceElements"];
          foreach (var trace in st)
            {
              Console.WriteLine(
                "'t{0}: {1}",
                trace["function"],
                trace["lineNumber"]);
            }
          }
        }
        else
        {
          // The result provided by the API needs to be cast into
          // the correct type, based upon what types the Apps
          // Script function returns. Here, the function returns
          // an Apps Script Object with String keys and values.
          // It is most convenient to cast the return value as a JSON
          // JObject (folderSet).
          return true;
        }
      }
      catch (Google.GoogleApiException e)
      {
        // The API encountered a problem before the script
        // started executing.
        Console.WriteLine(DateTime.Now.ToString("HH:mm:ss") + " Could not call Apps Script");
      }
      return false;
    }
...

以上两段代码一起使用，完美地解决了问题。不同数据量之间的执行时间差别不大，传输过程中也没有数据损坏。