Guest User

CopyDown

a guest
Sep 30th, 2012
311
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.91 KB | None | 0 0
  1. var scriptTitle = "copyDown Script V1.0 (6/12/12)";
  2. // Written by Andrew Stillman for New Visions for Public Schools
  3. // Published under GNU General Public License, version 3 (GPL-3.0)
  4. // See restrictions at http://www.opensource.org/licenses/gpl-3.0.html
  5. // Support and contact at http://www.youpd.org/autocrat
  6.  
  7. var COPYDOWNIMAGEURL = "https://sites.google.com/a/newvisions.org/data-dashboard/searchable-docs-collection/copyDown_icon.gif?attredirects=0";
  8.  
  9. function onOpen() {
  10. var ss = SpreadsheetApp.getActiveSpreadsheet();
  11. var menuEntries = [ {name: "What is copydown?", functionName: "whatIs"},
  12. {name: "Set triggers", functionName: "setTriggers"},
  13. {name: "Manually refresh", functionName: "copydown"}];
  14. ss.addMenu("copyDown", menuEntries);
  15. }
  16.  
  17.  
  18. function copydown() {
  19. setCopyDownUid();
  20. setCopyDownSid();
  21. logCopyDown();
  22. var ss = SpreadsheetApp.getActiveSpreadsheet();
  23. var sheets = ss.getSheets() [8];
  24. var cellAddresses = new Object();
  25. for (var i=0; i<sheets.length; i++) {
  26. var range = sheets[i].getDataRange();
  27. var lastRow = range.getLastRow();
  28. var values = range.getValues();
  29. for (var j=0; j<values.length; j++) {
  30. for (var k=0; k<values[j].length; k++) {
  31. var test = values[j][k].toString();
  32. var start = test.indexOf("copydown");
  33. if (start == 0) {
  34. start = start+10;
  35. var end = test.length-2;
  36. var length = end-start;
  37. var value = test.substr(start, length);
  38. var col = k+1;
  39. var nextRow = j+2;
  40. var numRows = lastRow-(nextRow-1);
  41. if (numRows>0) {
  42. var destRange = sheets[i].getRange(nextRow, col, numRows, 1);
  43. destRange.clear();
  44. var newLastRow = sheets[i].getDataRange().getLastRow();
  45. var newNumRows = newLastRow-(nextRow-1);
  46. var newDestRange = sheets[i].getRange(nextRow, col, newNumRows, 1);
  47. var cell = sheets[i].getRange(nextRow-1, col);
  48. cell.setFormula(value);
  49. cell.copyTo(newDestRange);
  50. }
  51. var cellAddress = cell.getA1Notation();
  52. cellAddresses[cellAddress] = test;
  53. }
  54. }
  55. }
  56. Utilities.sleep(500);
  57. resetCellValues(cellAddresses, sheets[i]);
  58. }
  59. }
  60.  
  61.  
  62. function resetCellValues(cellAddresses, sheet) {
  63. for (var cellAddress in cellAddresses) {
  64. sheet.getRange(cellAddress).setValue(cellAddresses[cellAddress]);
  65. }
  66. }
  67.  
  68. function setTriggers () {
  69. var app = UiApp.createApplication().setTitle("Set Triggers");
  70. var ss = SpreadsheetApp.getActiveSpreadsheet();
  71. var panel = app.createVerticalPanel().setTitle("Set Triggers").setId("panel");
  72. var label = app.createLabel();
  73. var text = "Triggers will automatically refresh any occurrances of \"copydown\" in your spreadsheet when a given action occurs.";
  74. 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.";
  75. text += "Avoid using the \"trigger on spreadsheet edit\" option unless your use case requires it, as it can be quite annoying.";
  76. label.setText(text);
  77. panel.add(label);
  78. var checkbox1 = app.createCheckBox('trigger on form submit').setId('formSubmit').setName('formSubmit').setStyleAttribute("margin", "10px 10px 10px 10px");
  79. populateValue(app, 'formSubmit', 'formSubmit');
  80. var checkbox2 = app.createCheckBox('trigger on spreadsheet edit').setId('edit').setName('edit').setStyleAttribute("margin", "10px 10px 10px 10px");
  81. populateValue(app, 'edit', 'edit');
  82. var handler = app.createServerHandler('saveTriggers').addCallbackElement(panel);
  83. var button = app.createButton('Save settings').addClickHandler(handler);
  84. panel.add(checkbox1);
  85. panel.add(checkbox2);
  86. panel.add(button);
  87. app.add(panel);
  88. ss.show(app);
  89. return app;
  90. }
  91.  
  92.  
  93. function saveTriggers (e) {
  94. var app = UiApp.getActiveApplication();
  95. var ssKey = SpreadsheetApp.getActiveSpreadsheet().getId();
  96. var formSubmit = e.parameter.formSubmit;
  97. ScriptProperties.setProperty('formSubmit', formSubmit);
  98. var triggers = ScriptApp.getScriptTriggers();
  99. if (formSubmit=="true"){
  100. var alreadySet = false;
  101. for (var i=0; i<triggers.length; i++) {
  102. if ((triggers[i].getEventType() == 'ON_FORM_SUBMIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
  103. alreadySet = true;
  104. }
  105. }
  106. if (alreadySet!=true) {
  107. ScriptApp.newTrigger('copydown').forSpreadsheet(ssKey).onFormSubmit().create();
  108. }
  109. }
  110. if (formSubmit=="false"){
  111. for (var i=0; i<triggers.length; i++) {
  112. if ((triggers[i].getEventType() == 'ON_FORM_SUBMIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
  113. ScriptApp.deleteTrigger(triggers[i]);
  114. }
  115. }
  116. }
  117.  
  118. var edit = e.parameter.edit;
  119. ScriptProperties.setProperty('edit', edit);
  120. if (edit=="true"){
  121. var alreadySet = false;
  122. for (var i=0; i<triggers.length; i++) {
  123. if ((triggers[i].getEventType() == 'ON_EDIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
  124. alreadySet = true;
  125. }
  126. }
  127. if (alreadySet!=true) {
  128. ScriptApp.newTrigger('copydown').forSpreadsheet(ssKey).onEdit().create();
  129. }
  130. }
  131. if (edit=="false"){
  132. for (var i=0; i<triggers.length; i++) {
  133. if ((triggers[i].getEventType() == 'ON_EDIT')&&(triggers[i].getHandlerFunction()=='copydown')) {
  134. ScriptApp.deleteTrigger(triggers[i]);
  135. }
  136. }
  137. }
  138. app.close();
  139. return app;
  140. }
  141.  
  142.  
  143. function populateValue(app, formId, propertyName) {
  144. var formElement = app.getElementById(formId);
  145. if (ScriptProperties.getProperty(propertyName)) {
  146. var value = ScriptProperties.getProperty(propertyName);
  147. Logger.log(value);
  148. if (value=="true") {
  149. value = true;
  150. }
  151. if (value=="false") {
  152. value = false;
  153. }
  154. formElement.setValue(value);
  155. }
  156. return app;
  157. }
Advertisement
Add Comment
Please, Sign In to add comment