Link to home
Start Free TrialLog in
Avatar of Zac123
Zac123Flag for United Kingdom of Great Britain and Northern Ireland

asked on

onOpen

hi, i wrote this little bit of code myself. i think if you look at it you'll see what i'm trying to achieve...
onOpen, set value of I1 to No


function onOpen2() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange ("I1");
cell.setValue("No");
  
}

Open in new window


it runs through the script ok without error but nothing happens. i have setup the triggers as well.
Avatar of Mike in IT
Mike in IT
Flag of United States of America image

If you are using VBA in Excel why change
var cell = sheet.getRange ("I1");

Open in new window

to
Range("I1").value = "No"

Open in new window

?

I find it easier to use VBA with excel than trying to use any other language.
Avatar of Zac123

ASKER

Hi, thanks. i'm using google sheets, does that matter?
Avatar of Norie
Norie

Is the code definitely running?
Avatar of Zac123

ASKER

changed my code to
function onOpen2() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 Range = ("I1").value = "No";
}

Open in new window


but it doesnt work. no error.
Avatar of Zac123

ASKER

yep i have set the triggers up so i think it should be running
Yes, using Google Sheets vs MS Excel does matter. They would use different formulas. i don't know enough about Google Sheets to give a better example, but these might help:

Cell Refences

Google Functions List
Use :
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("I1");
  range.setValue("No");

Open in new window


or :
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("I1").setValue("No");

Open in new window

so did it worked?
If you are not using Excel, but instead are using Google Sheets, you should remove the Excel tag.
Avatar of Zac123

ASKER

hiya,

with this code:
function onOpen2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("I1");
  range.setValue("No");
  
}

Open in new window


it works but it places it on whatever tab is first. could i specify a tab called 'dataEntry' please?
ASKER CERTIFIED SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zac123

ASKER

your code is obviously fine but i think it is not working because i already use a dropdown in that col. would this stop the onOpen from working?
I don't see any other reason
Avatar of Zac123

ASKER

thanks so much for your extended help. i made a very silly mistake.... your code works perfectly but i was telling you I1 and should have been I2. very sorry about this...

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("dataEntry");  
  var range = sheet.getRange("I2");
  range.setValue("No");