How to create a Google Sheets Script that runs as soon as the user opens a spreadsheet?

Question

How do I create a script that runs as soon as the user opens a spreadsheet?

Specifically I need a script that when the spreadsheet is opened asks the name and sex of the user, and depending on the gender it adds the name to a different sheet of the spreadsheet.

This is simple but I've gone through the tutorials on their webpage and couldn't do it. I know how to program but I am new to Google Apps Scripts.

Also is this something done better in Google Forms or in Google Sheets?

Answer 1

How do I create a script that runs as soon as the user opens a spreadsheet?

An onOpen() trigger function.

Specifically I need a script that when the spreadsheet is opened asks the name and sex of the user, and depending on the gender it adds the name to a different sheet of the spreadsheet.

The onOpen() should call a function that uses Browser.inputBox() to get the user's input, then writes it to the sheet via Range.setValues().

Note that the user will need to have edit privileges for this to work.

Also is this something done better in google forms or in google spreadsheets?

If you want the UI to show up in a spreadsheet, then the script must be contained in a spreadsheet.

Alternatively, if you don't want the user to see the spreadsheet, you could use the Forms service to collect their input, with no need for any programming.

Answer 2

Nothing too hard to get that :

read this doc on how to build an alert in a spreadsheet and make it show up using an installable trigger onOpen

all you have to do is put these together.

Answer 3

For script lauched at opening you need write function:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  ...
};

Google Spreadsheet don't have a dialog window or something similar. But there are several ways:

1. You can use show method to show your html or UiApp input dialogbox: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#show(Object)
2. you can use one sheet for input "sex" and hide others sheets. After click button you can show all sheets. Here is SpreadSheet API: https://developers.google.com/apps-script/reference/spreadsheet/
3. you can use information from profile logged person and read his/her sex :)
4. you can use Form for input "sex" and switch context (by script) to spreadsheet but this is more complex solution. Here is Form API: https://developers.google.com/apps-script/reference/forms/

Yea, I forget about inputBox() :)

Answer 4

To create a script that runs as soon as the user open the spreadsheet, you should create a bounded to a spreadsheet script and use a trigger:

1. Create a new or open an existing spreadsheet.
2. Click on Tools > Script editor...
3. Click the close button on the welcome dialog.
4. Start writing your script

To make the script run on open, you could use a simple trigger by using onOpen as the function name or you could use an installable trigger.

For further details see https://developers.google.com/apps-script/guides/sheets