I'm no wizard, but I've been playing around trying to learn a bit with google apps script and I'm just confused as to why it isn't working. I've looked through the documentation and have referenced other works using if else scripts and I can't find the error. Context: E7 is a checkbox, F7 is a fill-in-the-blank.
function testO() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var getRange = mySS.getRange("E7")
if(getRange = true){
mySS.getRange("E7").setValue("FALSE")
}
else{
mySS.getRange("F7").clearContent()
}
}
CodePudding user response:
It looks that you need to spend some time learning the pretty basics of JavaScript. Please start by reading https://developers.google.com/apps-script/guides/sheets as it gives helpful pointers like,
- Google Apps Script programming language is JavaScript
- How to read and write to a Google Sheets spreadsheet.
Regarding the specifics of your code,
- in the
mySs.getRange
methods, instead ofA1
useSheet1!A1
style references - in the comparison expressions for equality in your if statement, instead of the assign operator
=
use abstract equality==
or strict equality===
operators, but in this very specific case it might be better to useRange.isChecked()
. - There are specific methods for working with checkboxes:
Range.isChecked()
,Range.uncheck()
,Range.check()
;
Below is a proposal to fix your script:
function testO() {
var mySS = SpreadsheetApp.getActiveSpreadsheet();
var getRange = mySS.getRange("Sheet1!E7");
if(getRange.isChecked()){
getRange.uncheck();
}
else{
mySS.getRange("Sheet1!F7").clearContent();
}
}
Notes:
Google Apps Script use the same method name for some methods that return the same type of object, i.e.
Spreadsheet.getRange
,Sheet.getRange
, but they might require different parameters,Spreadsheet.getRange
might useA1
orSheet1!A1
style references. When usingA1
it will get the cellA1
from the first sheet.Sheet.getRange
might useA1
style but it will return the cellA1
from the corresponding sheet.
CodePudding user response:
A simple example with some explanation
function testO() {
const ss = SpreadsheetApp.getActive();//I like havign access to the spreadsheet as a single variable. While it may not be important now it might be in the future.
const sh = ss.getSheetByName("Sheet0");//For clerity sake it is better to identify the sheet you are going to. In your code you depended upon the fact that the spreadsheet has a default active sheet which is ss.getSheets()[0]
const rg = sh.getRange("E7");//getRange returns a range no a value
const v = rg.getValue();//getValue returns a single value
if(v == true){ //= is an assignment == is a comparison
sh.getRange("E7").setValue("FALSE");//This is usually the value reserved for an unchecked checkbox
}
else{
rg.clearContent()
}
}