Monday, December 31, 2012

Powerful entry-level script for copying sheets

Time saving script
I've been using this short script in Google Apps Scripts lately and thought I'd share it since it has been saving me a lot of time. What it will do is take an existing sheet in another spreadsheet and copy it into your current working spreadsheet. It will pull all graphs, formatting, formulas and values in with the copy. *Chart settings will be preserved but for some reason right now you have to go in and reenter the chart range as that seems to reset. <--Google since you are discontinuing Gadgets could you please fix this for those of us now needing to convert our work over to charts??

Getting started
To use it, you'll need to copy the function below, go into your spreadsheet that you want to copy the sheet into and click on the Tools menu and then select "Script Editor". Here, create a "New Script" from the file menu and paste this in:


function copySheet() {
  SpreadsheetApp.openById("SpreadsheetKeyIDHere").getSheetByName("Sheet name exactly as it appears on the file you are copying").copyTo(SpreadsheetApp.getActiveSpreadsheet()).setName("Name that you'd like the sheet to be called when it's copied");
  }

You will need to edit all the parts in the script above that appear red. Please notice that the " are not in red and should remain in the function after you edit your information.

The Key ID is the portion of the web address of the file you are wishing to copy between the key= and the # symbol:
https://docs.google.com/spreadsheet/ccc?key=0ApfEUqF5qtzJdEh2QndSWGk5cG44RVNjUWhSR3ZPSUE#gid=3

Here's an example of an edited function:

function copySheet() {
  SpreadsheetApp.openById("0ApfEUqF5qtzJdEh2QndSWGk5cG44RVNjUWhSR3ZPSUE").getSheetByName("Test Analysis").copyTo(SpreadsheetApp.getActiveSpreadsheet()).setName("Test Analysis");
  }

For those that are users of my course management data dashboard, the above example script is the exact function you'd need to create a replacement for the Test Statistics tab that will stop working when Gadgets are discontinued. You'll need to copy the existing ranges for the charts from your old Test Statistics sheet before the charts start working.
Range for the first chart will be TestItemData!AG1:AH16
Range for the second will be TestItemData!W1:X55
To edit the chart range, click on the chart, click on the downward pointing arrow, then click Advanced Edit, click on the start tab and edit the range to what is shown for each chart. Don't change anything else and click Done.

No comments:

Post a Comment