Creating a prompt that opens upon refreshing a Google Sheet

Question

Creating a spreadsheet that will upon either being opened or refreshed should pop up a prompt asking me for the day, store that value in a particular cell and then proceed to ask me a series of other questions.

This is what I have so far:

function onOpen()
{
 // Prompt for the value
 const day =  SpreadsheetApp.getUi().prompt("Please enter the day.").getResponseText();
 //Get all pages in spreadsheet and iterate through
 const sheets =   SpreadsheetApp.getActiveSpreadsheet().getSheets();
 for(var i = 0; i < sheets.length; i++ )
 {
  //Set the value in the cell B3 of the page
  sheets[i].getRange("B2").setValue( day );
 }
 if (day.toLowerCase() === 'monday') {
   function martialArts() {
     const mma = ui.alert("Did you go to MMA training today?".ui.ButtonSet.YES_NO);
     if(mma == ui.Button.YES) {
       sheets.getRange('C2').setValue(mma)
     }
    }
 }  
}

When I run the script I only get a prompt asking for the day, if I enter Monday this is stored by the other prompt (the question "Did you go to MMA training today") does not run. Additionally, this script does not run upon opening or refreshing my sheet

score 0 · Answer 1 · answered Jan 12 '22 at 02:58

Simple triggers have limitations, instead use and installable trigger.

Reference

score 0 · Answer 2 · answered Jan 12 '22 at 09:49

There are some things wrong on your code:

1 - On the const day you are using SpreadsheetApp.getUi() but on the const mma you are using ui which is not defined.

2 - You have a function inside an if with function martialArts(), that should be removed.

3 - On the for loop you are selecting the sheet with sheets[i] but then on the if below you select sheets which is all the sheets in the spreadsheet. If you know there is only one sheet or you know which sheets has to be selected you can use sheets[0] or set the if inside the for loop.

With this changes applied your code should look like this:

function onOpen() {
  // Prompt for the value
  var ui = SpreadsheetApp.getUi();
  const day = ui.prompt("Please enter the day.").getResponseText();
  //Get all pages in spreadsheet and iterate through
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  sheets[0].getRange("B2").setValue(day);
  if (day.toLowerCase() === 'monday') {
    const mma = ui.alert('Did you go to MMA training today?', ui.ButtonSet.YES_NO);
    if (mma == ui.Button.YES) {
      sheets[0].getRange('C2').setValue(mma)
    }
  }
}

Creating a prompt that opens upon refreshing a Google Sheet

2 Answers2