Friday, April 13, 2012

Zip file for Google Docs

Most of what we had in the late 90's for computers I'm glad to be past, but I've found myself wanting .zip files a few times while working with Google Spreadsheets that act as a package. Below is some script I found and modified to make a copy of an existing template I've shared from a menu command. I'll be using this to automate the process for setting up unit dashboards and from those, individual Google Forms assessments, essentially allowing me to create packaged templates now that can be quickly customized to speak to each other.
Each copy will be made from a central file that will give the option to connect an already existing class list or make a new one for new users. The large benefit of this is no longer needing to have a local template copy saved as a new one will be created and set up from a menu command each time. This should also alleviate some of the work in the initial setup for those not quite understanding how Key ID's work in Google Docs. I'm hoping to share it soon and will have a video showing how it all works.
In the script below I tried to explain parts in case a person would want to modify it for their own uses.

function CreateUnitDash(){
var current = SpreadsheetApp.getActiveSpreadsheet().getId();
var template = DocsList.getFileById("0ApfEUqF5qtzJdHNOcGUwN0c4YXVpQ3o4NnhUS3lRRnc");//This line holds the Key ID to the file you are copying.
var UnitName = Browser.inputBox("Enter the name of your unit","", Browser.Buttons.OK);//This line allows the user to name the new file copy.

  var DashKey = template.makeCopy(UnitName).getId();
  var GetClassList = ss.getSheetByName("Smart Setup").getRange(1, 6).getValue();//This line is getting information from the host sheet and sending it to a location in the new file.
SpreadsheetApp.openById(DashKey).getSheetByName("Setup").getRange(1,6).setValue(GetClassList);//Sets the value in the new sheet at row 1, column 6 in sheet "Setup"
SpreadsheetApp.openById(current);//This line reactivates the original host spreadsheet as the active sheet (rather than the copied sheet)

  var Link = ""+DashKey;//Creates a link to the new copy.

  var lastRow = ss.getSheetByName("Smart Setup").getLastRow();//Gets the last row number that has data so the nothing will be overwritten.
  ss.getSheetByName("Smart Setup").getRange(lastRow+1, 1).setValue(UnitName);//These lines send the value stored in each
  ss.getSheetByName("Smart Setup").getRange(lastRow+1, 2).setValue(DashKey);///variable to a specified location in the
  ss.getSheetByName("Smart Setup").getRange(lastRow+1, 3).setValue(Link);//////active host spreadsheet.

No comments:

Post a Comment