Google Sheets Script Error - Cannot convert [objec

问题:

I'm trying to create a script that creates a new spreadsheet in my Google drive, and then merges the sheets from a specific Google drive folder into the newly created spreadsheet.

As of right now I'm getting the following error: Cannot convert [object Object] to Spreadsheet. (line 34, file "Code")

I've copied the code below.

 function mergeSheets() {

  /* Retrieve the desired folder */
  var myFolder = DriveApp.getFoldersByName("Merging Spreadsheet Test Folder").next();

  /* Get all spreadsheets that resided on that folder */
  var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");


var newSpreadSheet = {
    "title": "Test Merge Document",
    "mimeType": "application/vnd.google-apps.document",
    "parents": [
      {
       "id": "0B4DgXVz2cL8oRURkdUNCZEhITG8"
      }
    ]
  };

    Drive.Files.insert(newSpreadSheet);

  /* Iterate over the spreadsheets over the folder */
  while(spreadSheets.hasNext()) {

    var sheet = spreadSheets.next();

    /* Open the spreadsheet */
    var spreadSheet = SpreadsheetApp.openById(sheet.getId());

    /* Get all its sheets */
    for(var y in spreadSheet.getSheets()) {

      /* Copy the sheet to the new merged Spread Sheet */
      spreadSheet.getSheets()[y].copyTo(newSpreadSheet); 
    }
  }      
 }     

Any help would be greatly appreciated.

回答1:

You are trying to save a sheet to an object not a Spreadsheet. There are two ways to fix this.

You can create the spreadsheet with:

var newSpreadSheet = SpreadsheetApp.create("Test Merge Document");

or open the sheet after you make it:

var newSpreadSheet = {
    "title": "Test Merge Document",
    "mimeType": "application/vnd.google-apps.document",
    "parents": [
      {
       "id": "0B4DgXVz2cL8oRURkdUNCZEhITG8"
      }
    ]
  };

var newSS =  Drive.Files.insert(newSpreadSheet),
    newSpreadSheet = SpreadsheetApp.openById(newSS.id);

回答2:

Here is the solution I ended up using. Works like a charm.

 function mergeSheets() {

 /* Retrieve the desired folder */
 var myFolder = DriveApp.getFoldersByName("Enter Folder Name Here").next();

 /* Get all spreadsheets that resided on that folder */
 var spreadSheets = myFolder.getFilesByType("application/vnd.google-apps.spreadsheet");    

 /* Creates new spreadsheet to merge documents into */
var newSpreadSheet = SpreadsheetApp.create("Enter Name For New Spreadsheet Here");

/* Iterate over the spreadsheets over the folder */
while(spreadSheets.hasNext()) {
var sheet = spreadSheets.next();

/* Grabs name of the document */    
var ssName = sheet.getName();
Logger.log(ssName)

/* Open the spreadsheet */
var spreadSheet = SpreadsheetApp.openById(sheet.getId());

/* Get all its sheets */
for(var y in spreadSheet.getSheets()) {

      /* Copy the sheet to the new merged Spread Sheet */
      spreadSheet.getSheets()[y].copyTo(newSpreadSheet).setName(ssName);
    }

  }      
 }