Script function could not be found

Question

I have created a rubric that has cells that change color when "xx" is added to the cell. Rather than type into each individual cell when assessing my students I wanted to have the xx's get added to the cell from clicking ion the cell. That's when I started searching around and started to investigate scripts. I added 2 drawings to each cell so I could click on one image to add the "xx" and click on the other image to delete the "xx" (if I changed their assessment rating). I have played with them today and found success in the first cell, but every other cell that I try to assign a script to says that the script can't be found. When I "run" the script from the script file, it shows up on my sheet, but not when I click on the cell like I want.

Here is a section of my code for the first row of the rubric (I have 15 rows with different titles-Verbal Communication, Authority, etc.):

Top row of rubric pre-click

Top row of rubric post-click

[

function verbalcommunication4() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("D5").getValue()
var plusVal= currVal +" xx"
s.getRange("D5") .setValue(plusVal)
}
function verbalcommunication4clear() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("D5").getValue()
var text =s.getRange("D5").getValue().replace(" xx","");
  s.getRange("D5").setValue(text);
}
function verbalcommunication3() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("E5").getValue()
var plusVal= currVal +" xx"
s.getRange("E5") .setValue(plusVal)
}
function verbalcommunication3clear() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("E5").getValue()
var text =s.getRange("E5").getValue().replace(" xx","");
  s.getRange("E5").setValue(text);
}
function verbalcommunication2() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("F5").getValue()
var plusVal= currVal +" xx"
s.getRange("F5") .setValue(plusVal)
}
function verbalcommunication2clear() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("F5").getValue()
var text =s.getRange("F5").getValue().replace(" xx","");
  s.getRange("F5").setValue(text);
}
function verbalcommunication1() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("G5").getValue()
var plusVal= currVal +" xx"
s.getRange("G5") .setValue(plusVal)
}
function verbalcommunication1clear() {
ss=SpreadsheetApp.getActiveSpreadsheet()
s=ss.getActiveSheet()
var currVal=s.getRange("G5").getValue()
var text =s.getRange("G5").getValue().replace(" xx","");
  s.getRange("G5").setValue(text);
}

verbalcommunication4 and verbalcomunication4clear work just fine. Every other script I try to attach to the drawings in the other cells attach with no error, but when I click on the object to run the script I get the error message "script function 'script name' could not be found." Like I said, when I run the script from the script project page the desired result (xx in the appropriate cell) appears, just not when I click on the cell's drawing like I want.

Top row after script is "run" from project page

Top row after cell E5's drawing is clicked

I'm new to this so any help would be appreciated. Thanks!

Answer 1

Your code is using global variables instead of local variables and it's not using sentence terminators (semicolons) on all the code lines that could have them.

While it's not mandatory to do in a different way, this could lead to some unexpected results, specially when we aren't pretty sure of how Google Apps Script and JavaScript works.

Note: Add spaces before and after each variable assignment operator, like =, for readability.

Replace each

 ss=SpreadsheetApp.getActiveSpreadsheet()

by

 var ss = SpreadsheetApp.getActiveSpreadsheet();

and so on.

Also, remove the space and add an ending semicolon on

s.getRange("E5") .setValue(plusVal)

The resulting statement should look like this:

s.getRange("E5").setValue(plusVal);

Do the same on the other similar statements.

References

• Do you recommend using semicolons after every statement in JavaScript?
• What is the purpose of the var keyword and when should I use it (or omit it)?
• var - MDN JavaScript

Answer 2

You will have to setup the conditional formatting and this script puts all of the buttons on a sidebar because it's easier to do. You can play with the styling to get what you want.

I used function vc and function vcc along with some smaller intermediate functions so that I wouldn't have to write them all out. But they're basically the same functions you had with the exception of the local variables via the var.

function vc(a1) {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet()
  var cv=sh.getRange(a1).getValue();
  var pv= cv +" xx";
  sh.getRange(a1).setValue(pv);
  SpreadsheetApp.flush();  
}

function vcc(a1) {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sh=ss.getActiveSheet();
  var cv=sh.getRange(a1).getValue();
  var txt =sh.getRange(a1).getValue().replace(" xx","");
  sh.getRange(a1).setValue(txt);
  SpreadsheetApp.flush();
}

function vcD5(){
  vc('D5');
}
function vccD5(){
  vcc('D5');
}
function vcE5(){
  vc('E5');
}
function vccE5(){
  vcc('E5');
}
function vcF5(){
  vc('F5');
}
function vccF5(){
  vcc('F5');
}
function vcG5(){
  vc('G5');
}
function vccG5(){
  vcc('G5');
}

And this function creates the sidebar with all of the buttons.

function createSideBar(){
  var vA=['D5','E5','F5','G5']
  var vB=['green','yellow','red','blue']
  var s='<style>input[type="button"]{padding:2px;margin:2px;width:100px;height:35px;}</style>';
  for(var i=0;i<4;i++){
    s+=Utilities.formatString('<div style="border:2px solid %s;"><br /><input type="button" value="%s" onClick="google.script.run.vc%s();" />',vB[i],vA[i],vA[i]);   
    s+=Utilities.formatString('<input type="button" value="%sc" onClick="google.script.run.vcc%s();" /></div>',vA[i],vA[i]);   
  }
  s+='<br /><input type="button" value="Close" onClick="google.script.host.close()" />';
  var userInterface=HtmlService.createHtmlOutput(s);
  SpreadsheetApp.getUi().showSidebar(userInterface);
}