Google Spreadsheet Script to Copy Sheet to multiple Spreadsheets at a time

Question:

( by LHawk )

I am trying to use Google Spreadsheets in my classroom to share rubric results with students. So each student has their own spreadsheet with a page for each quiz/test etc. I have one master copy. For each new quiz, I create a new page there and then copy it into each student's spreadsheet. I have 2 issues:

1) Even though the "Copy to" option opens up to a screen with checkboxes, it only allows me to check one box at a time. Then I must wait for a confirmation that the transfer was successful, hit "okay" and repeat for every student. This is a major waste of time. I will put up with it for now, but there is no way my colleagues will be willing to try this out like this. How can I select all the relevant spreadsheets? (Or how do I ask Google to make that change?)

2) The pages appear in the students' spreadsheets with the new name "Copy of Quiz 2" instead of just "Quiz 2." This isn't a major problem, but looks unprofessional and requires me to change the name in my master copy so that I can do formulas that reference other pages. (It is certainly not worth the time to manually change the name of the page for every student.)

Thank you,

Laura

Browser: Chrome 

OS: Mac, Mountain Lion

Solution:

To copy the current Active Sheet to other Spreadsheets (that can be more than one), copy the following script in your script editor:




function onOpen(){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Copy Active Sheet to Other Spreadsheets", functionName: "doGet"});
  spreadsheet.addMenu("Copy Sheet", menuEntries);
}

function doGet()
{
  var app = UiApp.createApplication();
  app.setTitle("Kishan - Copy Sheet in Multiple Spreadsheets");
  
  var form = app.createFormPanel();
  var flow = app.createFlowPanel();
  
  var label = app.createLabel("Select Spreadsheet where you want to copy the current sheet:").setId('selectLabel');
  flow.add(label);
  var allfiles =  DocsList.getAllFiles();
  
  var verticalPanel = app.createVerticalPanel().setId('verticalPanel');
  
  for(var i=0;i<allfiles.length;i++)
  {
    var temp = app.createCheckBox(allfiles[i].getName()).setName('cb'+i).setId('cb'+i);
    var tempvalue = app.createHidden('cbvalue'+i, allfiles[i].getId());
    verticalPanel.add(temp);
    verticalPanel.add(tempvalue);
  }

  var scrollPanel = app.createScrollPanel().setId('scrollPanel');
  scrollPanel.add(verticalPanel);
  scrollPanel.setSize("400", "250")
  flow.add(scrollPanel);
  
  var buttonsubmit = app.createSubmitButton("Copy");
  flow.add(buttonsubmit);
  
  form.add(flow);
  app.add(form);
  
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

function doPost(eventInfo) {
  var app = UiApp.getActiveApplication();
  var allfiles =  DocsList.getAllFiles();
  var tempSsId = "";
  
  for(var i=0;i<allfiles.length;i++)
  {
    var temp = eventInfo.parameter['cb'+i];
    if(temp == 'on')
    {
      tempSsId = eventInfo.parameter['cbvalue'+i];
      var activeSheet = SpreadsheetApp.getActiveSheet().copyTo(SpreadsheetApp.openById(tempSsId));
      activeSheet.setName(SpreadsheetApp.getActiveSheet().getSheetName());
    }
  }
  
  var label = app.createLabel('statusLabel');
  label.setText("Copied Active sheet in all selected Spreadsheets...");
  label.setVisible(true);
  app.add(label);
  return app;
}




And If you are not much familiar with scripts then check out the following link:

http://igoogledrive.blogspot.in/2012/08/how-to-write-script-in-google.html 

Note: After copying the above script in to the script editor, either refresh your spreadsheet or close it and open it again. Then you will notice the "Copy Sheet" Menu entry at the menu bar, that is near Help menu.
And under "Copy Sheet", you will be able to view "Copy Active Sheet to Other Spreadsheets", have a look at the following screenshot:

Now, on click this "Copy Active Sheet to Other Spreadsheets", when running this script for the first time you will have to authorize this script for one time and then you will be able to run it anytime. So after authorization, you will notice that a app will load, have a look at the following screenshot:

Now, select the spreadsheets where you want to copy the Active Sheet and then press the button "Copy", now allow the script some time to work.. after few mins you will see the copy of the Active Sheet in the selected spreadsheets and having name as "ActiveSheetName" instead of "Copy of ActiveSheetName".

Note: Run this script from the screen of the Active Sheet that you want to copy, that mean if you want to copy Sheet1 then first open Sheet1 and then run this script. Whichever Sheet will be active (that is whichever sheet will opened) will be copied to the selected spreadsheets.


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive