CODEWITHSUNDEEP
javascriptloopsgoogle-apps-scriptgoogle-sheets
James D
James D

Reputation: 3142

Copy several sheets from one spreadsheet to another spreadsheet

I'm trying to create a script to copy several sheets from one (unopened) spreadsheet to another (active spreadsheet).

I have searched through other solutions and using some other suggestions I have put together this piece of code. I have no programming experience and just started learning Javascript when my school started using Google apps.

It has taken me about 5 hours get this far and I think I am almost there, but I can't get it to work.

function copySheets() {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var infoPageSheet = ss.getSheetByName("infoPage");
    var sheetID = infoPageSheet.getRange("B11").getValues().toString(); //gets the ID of the spreadsheet that contains the sheets I need to copy from cell B11 
    var classSelect = infoPageSheet.getRange("B12:B17").getValues().toString().split(","); // gets the names of the sheets I need to copy (one name per cell)
    var stuNameList = SpreadsheetApp.openById(sheetID); // opens the target sheet

    for (var i = 0; i < classSelect.length; i++) {
        var copy = stuNameList.getSheetByName(classSelect[i]);
        if (copy) {
            Logger.log("copy " + classSelect[i] + " already exists");
        } else {
            stuNameList.copyTo(ss).setName(classSelect[i]).showSheet();
        } 
    }
}

I will input the ID and the names of the sheets I want to copy into the active spreadsheet. I will run the script and the script should get the sheets from the source file (unopened) and copy them to the active spreadsheet with the same name.

Please feel free to be as critical of my code as you want, any help would be greatly appreciated..

UPDATE

I have got this working now 

function getVars() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sortSheet = ss.getSheetByName("Sorted Scores");
   var infoPageSheet = ss.getSheetByName("infoPage");
   var sheetID = infoPageSheet.getRange("B11").getValue().toString();
   var classSelect1 = infoPageSheet.getRange("B12:B18").getValues().toString().split(",");

   var classSelect = new Array();
     for (var i = 0; i < classSelect1.length; i++) {
        if (classSelect1[i]) {
          classSelect.push(classSelect1[i]);
  }
}
       var stuNameList = SpreadsheetApp.openById(sheetID);

      Logger.log(sheetID)
       for (var i = 0; i < classSelect.length; i++) {
        var get = (classSelect[i]);
        var copy = ss.getSheetByName(classSelect[i]);
           if (copy) {
             Logger.log("copy " + classSelect[i] + "already exists");
         } else {
            stuNameList.getSheetByName(get)
            .copyTo(ss)
            .setName(get);
             Logger.log(classSelect)
    }
  }
}

Upvotes: 0

Views: 149

Answers (2)

Jose Luis Santos Blaquera
Jose Luis Santos Blaquera

Reputation: 104

Your "sheetID" seems to be a big string with multiple sheet ids. What I mean to say is that

Logger.log(sheetID) //logs "sampleid,sampleid2,sampleid3"

I don't think openById() of stuNameList accepts that (returns a Bad value error). Try using .openById(sheetID[i]) on your else.

Upvotes: 1

Amit Agarwal
Amit Agarwal

Reputation: 11268

The copyTo method is only available for the Sheet class.

for (var i = 0; i < classSelect.length; i++) {
    var copy = stuNameList.getSheetByName(classSelect[i]);
    if (copy) {
        Logger.log("copy " + classSelect[i] + " already exists");
    } else {
        ss.getSheetByName(classSelect[i])
          .copyTo(stuNameList)
          .setName(classSelect[i]);
    } 
}

Upvotes: 0

Related Questions