( 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,
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?
ReplyDeleteHi Michael & Kishan,
DeleteI 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?
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?
DeleteNo clue mate, I have posted about it on the google forum too with no feedback as yet.
DeleteThis 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!
I encountered the same error. Any solution yet?
DeleteDo 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)
ReplyDeleteOh bummer. It worked the first time, but the subsequent 8 tries were unsuccessful. I refreshed, re-opened and refreshed again. Any suggestions?
ReplyDeleteYeah! 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!!
ReplyDeleteLooks 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?
ReplyDeleteThis comment has been removed by the author.
DeleteNew code which worked for me...
Deletefunction 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;
}
This comment has been removed by the author.
ReplyDeleteThere is little bit a problem. Why that a copied by script after I've run it the copied is not current sheet but always the first sheet of source which we're wanting to copies.
ReplyDeleteI get
ReplyDelete"Exception: UiApp has been deprecated. Please use HtmlService instead."
I guess I could expect that from a 2012 script :-)