Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- var scriptTitle = "copyDown Script V1.0 (6/12/12)";
- // Written by Andrew Stillman for New Visions for Public Schools
- // Published under GNU General Public License, version 3 (GPL-3.0)
- // See restrictions at http://www.opensource.org/licenses/gpl-3.0.html
- // Support and contact at http://www.youpd.org/autocrat
- var COPYDOWNIMAGEURL = "https://sites.google.com/a/newvisions.org/data-dashboard/searchable-docs-collection/copyDown_icon.gif?attredirects=0";
- function onOpen() {
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var menuEntries = [ {name: "What is copydown?", functionName: "whatIs"},
- {name: "Set triggers", functionName: "setTriggers"},
- {name: "Manually refresh", functionName: "copydown"}];
- ss.addMenu("copyDown", menuEntries);
- }
- function copydown() {
- setCopyDownUid();
- setCopyDownSid();
- logCopyDown();
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var sheets = ss.getSheets() [8];
- var cellAddresses = new Object();
- for (var i=0; i<sheets.length; i++) {
- var range = sheets[i].getDataRange();
- var lastRow = range.getLastRow();
- var values = range.getValues();
- for (var j=0; j<values.length; j++) {
- for (var k=0; k<values[j].length; k++) {
- var test = values[j][k].toString();
- var start = test.indexOf("copydown");
- if (start == 0) {
- start = start+10;
- var end = test.length-2;
- var length = end-start;
- var value = test.substr(start, length);
- var col = k+1;
- var nextRow = j+2;
- var numRows = lastRow-(nextRow-1);
- if (numRows>0) {
- var destRange = sheets[i].getRange(nextRow, col, numRows, 1);
- destRange.clear();
- var newLastRow = sheets[i].getDataRange().getLastRow();
- var newNumRows = newLastRow-(nextRow-1);
- var newDestRange = sheets[i].getRange(nextRow, col, newNumRows, 1);
- var cell = sheets[i].getRange(nextRow-1, col);
- cell.setFormula(value);
- cell.copyTo(newDestRange);
- }
- var cellAddress = cell.getA1Notation();
- cellAddresses[cellAddress] = test;
- }
- }
- }
- Utilities.sleep(500);
- resetCellValues(cellAddresses, sheets[i]);
- }
- }
- function resetCellValues(cellAddresses, sheet) {
- for (var cellAddress in cellAddresses) {
- sheet.getRange(cellAddress).setValue(cellAddresses[cellAddress]);
- }
- }
- function setTriggers () {
- var app = UiApp.createApplication().setTitle("Set Triggers");
- var ss = SpreadsheetApp.getActiveSpreadsheet();
- var panel = app.createVerticalPanel().setTitle("Set Triggers").setId("panel");
- var label = app.createLabel();
- var text = "Triggers will automatically refresh any occurrances of \"copydown\" in your spreadsheet when a given action occurs.";
- text += "Setting \"trigger on form submit\" can be extremely useful when calculations need to be kept up to date in columns to the right of form data.";
- text += "Avoid using the \"trigger on spreadsheet edit\" option unless your use case requires it, as it can be quite annoying.";
- label.setText(text);
- panel.add(label);
- var checkbox1 = app.createCheckBox('trigger on form submit').setId('formSubmit').setName('formSubmit').setStyleAttribute("margin", "10px 10px 10px 10px");
- populateValue(app, 'formSubmit', 'formSubmit');
- var checkbox2 = app.createCheckBox('trigger on spreadsheet edit').setId('edit').setName('edit').setStyleAttribute("margin", "10px 10px 10px 10px");
- populateValue(app, 'edit', 'edit');
- var handler = app.createServerHandler('saveTriggers').addCallbackElement(panel);
- var button = app.createButton('Save settings').addClickHandler(handler);
- panel.add(checkbox1);
- panel.add(checkbox2);
- panel.add(button);
- app.add(panel);
- ss.show(app);
- return app;
- }
- function saveTriggers (e) {
- var app = UiApp.getActiveApplication();
- var ssKey = SpreadsheetApp.getActiveSpreadsheet().getId();
- var formSubmit = e.parameter.formSubmit;
- ScriptProperties.setProperty('formSubmit', formSubmit);
- var triggers = ScriptApp.getScriptTriggers();
- if (formSubmit=="true"){
- var alreadySet = false;
- for (var i=0; i<triggers.length; i++) {
- if ((triggers[i].getEventType() == 'ON_FORM_SUBMIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
- alreadySet = true;
- }
- }
- if (alreadySet!=true) {
- ScriptApp.newTrigger('copydown').forSpreadsheet(ssKey).onFormSubmit().create();
- }
- }
- if (formSubmit=="false"){
- for (var i=0; i<triggers.length; i++) {
- if ((triggers[i].getEventType() == 'ON_FORM_SUBMIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
- ScriptApp.deleteTrigger(triggers[i]);
- }
- }
- }
- var edit = e.parameter.edit;
- ScriptProperties.setProperty('edit', edit);
- if (edit=="true"){
- var alreadySet = false;
- for (var i=0; i<triggers.length; i++) {
- if ((triggers[i].getEventType() == 'ON_EDIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
- alreadySet = true;
- }
- }
- if (alreadySet!=true) {
- ScriptApp.newTrigger('copydown').forSpreadsheet(ssKey).onEdit().create();
- }
- }
- if (edit=="false"){
- for (var i=0; i<triggers.length; i++) {
- if ((triggers[i].getEventType() == 'ON_EDIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
- ScriptApp.deleteTrigger(triggers[i]);
- }
- }
- }
- app.close();
- return app;
- }
- function populateValue(app, formId, propertyName) {
- var formElement = app.getElementById(formId);
- if (ScriptProperties.getProperty(propertyName)) {
- var value = ScriptProperties.getProperty(propertyName);
- Logger.log(value);
- if (value=="true") {
- value = true;
- }
- if (value=="false") {
- value = false;
- }
- formElement.setValue(value);
- }
- return app;
- }
Advertisement
Add Comment
Please, Sign In to add comment