Wednesday, October 10, 2012

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:

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:

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,

11 comments:

  1. I am attempting to add this ability to one of my documents, but when I try to copy the sheet it tells me that it encountered an error. Any idea why that might be happening?

    ReplyDelete
    Replies
    1. Hi Michael & Kishan,

      I also use this script - it works with some sheets, but another sheet I tried it on didn't work. Michael - do you have many formulas on the sheet you are trying to copy? Cause the one that gives me "an unexpected error" has. I thought maybe that is the problem?

      Delete
    2. The sheet that I am trying to copy doesn't have a whole lot of formulas, but it may have more than the average spreadsheet. Any ideas on how to fix this?

      Delete
    3. No clue mate, I have posted about it on the google forum too with no feedback as yet.

      This is a really super script when working...

      Maybe you should also post about it there and see if anyone replies...if they do let me know please!

      Delete
    4. I encountered the same error. Any solution yet?

      Delete
  2. Do you have a version that copies all sheets from *one* spreadsheet to another? (Basically I'm trying to "manually" migrate some Google spreadsheets with multiple tabs to the new Google Sheets)

    ReplyDelete
  3. Oh bummer. It worked the first time, but the subsequent 8 tries were unsuccessful. I refreshed, re-opened and refreshed again. Any suggestions?

    ReplyDelete
  4. Yeah! I found a work around. If I move the sheet I want to copy to the first position (far left) and then 'copy sheet', it works! THANK YOU for sharing this!!

    ReplyDelete
  5. Looks like this code will no longer work since DocsList has been deprecated by Google (see https://developers.google.com/apps-script/sunset). Can you please please please update the code?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. New code which worked for me...

      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 = DriveApp.getFiles();

      var verticalPanel = app.createVerticalPanel().setId('verticalPanel');
      var i = 0;
      while (allfiles.hasNext())
      {
      var file = allfiles.next();
      var temp = app.createCheckBox(file.getName()).setName('cb'+i).setId('cb'+i);
      var tempvalue = app.createHidden('cbvalue'+i, file.getId());
      verticalPanel.add(temp);
      verticalPanel.add(tempvalue);
      i++;
      }

      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 = DriveApp.getFiles();
      var tempSsId = "";

      var i = 0;
      while (allfiles.hasNext())
      {
      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());
      }
      i++;
      }

      var label = app.createLabel('statusLabel');
      label.setText("Copied Active sheet in all selected Spreadsheets...");
      label.setVisible(true);
      app.add(label);
      return app;
      }

      Delete