google sheets script - several multiple choice answers-CodePudding

I am trying to make a pop-up box with several multiple-choice answers to select.

I used this example: google apps script : a select drop down list in google sheet Which was a good starting point for 1 question, and I tried expanding on it to get multiple answers but failed expanding it.

Here's my test file: https://docs.google.com/spreadsheets/d/1BRCqpvfRl64a7ISyuohxUJLWKbqX9Fz6NPCrL2iKEm0/

It contains script code triggered by a simple button press;

function start() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:B1').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('A10').activate();

// START HTML POP-UP
dropDownModal()
};

// -------------------------------------------------------------------------

function dropDownModal() {
  var htmlDlg = HtmlService.createHtmlOutputFromFile('dropdown.html')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setWidth(350)
    .setHeight(175);
    
  SpreadsheetApp.getUi()
    .showModalDialog(htmlDlg, 'Box title');
};

function writeChoice(selection1) {
  const writeResponseLocation1 = "A1";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation1)
    .setValue(selection1);
}

function writeChoice(selection2) {
  const writeResponseLocation2 = "B1";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation2)
    .setValue(selection2);
}
// -------------------------------------------------------------------------

and this dropdown.html

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>
<script>
  function onSuccess1() {
    google.script.host.close();
  }

  function submit1() {
    const choice1 = document.getElementById('choice1').value;
    const choice2 = document.getElementById('choice2').value;

    google.script.run
      .withSuccessHandler(onSuccess1)
      .writeChoice(choice1)
      .writeChoice(choice2);
  }
  
  function setup1() {
    const button = document.getElementById('submitbutton1');
    button.addEventListener("click", submit1)
  }

</script>

<body onl oad="setup1()">
  <p>
    Text 1.
  </p>
  <form>
    <select id="choice1">
      <option value="choice 1-A">choice 1-A</option>
      <option value="choice 1-B">choice 1-B</option>
      <option value="choice 1-C">choice 1-C</option>
    </select>
    <br><br>
    <select id="choice2">
      <option value="choice 2-A">choice 2-A</option>
      <option value="choice 2-B">choice 2-B</option>
      <option value="choice 2-C">choice 2-C</option>
    </select>
    <br><br>
    <button id="submitbutton1">Hit it 1</button>
  </form>
</body>

</html>

and it's writing the answer from Question 1 into the location intended for Question 2.

Can someone please help find where I went wrong?

Thanks

CodePudding user response：

You have two functions with the same name `writeChoice`

It is not enough to change the name of the function parameters (selection1, selection1), indeed those are only placeholders.

Instead you need to either create two different funcitons with two different names (sample 1) or pass writeResponseLocation as a second parameter (sample 2).

Furthermore, with google.script.run you can only call one Apps Script function at a time. You can call the second one e.g. within your success handler.

Sample 1:

Code.gs

...
function writeChoice1(selection1) {
  const writeResponseLocation1 = "A1";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation1)
    .setValue(selection1);
}

function writeChoice2(selection2) {
  const writeResponseLocation2 = "B1";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation2)
    .setValue(selection2);
}
...

html

<script>
  function onSuccess1() {
    const choice2 = document.getElementById('choice2').value;
    google.script.run
      .withSuccessHandler(onSuccess2)
      .writeChoice2(choice2);
  }
  function onSuccess2() {
    google.script.host.close();
  }
  function submit1() {
    const choice1 = document.getElementById('choice1').value;
    google.script.run
      .withSuccessHandler(onSuccess1)
      .writeChoice1(choice1);
  }
  
  function setup1() {
    const button = document.getElementById('submitbutton1');
    button.addEventListener("click", submit1)
  }

</script>
...

Sample 2:

Code.gs

...
function writeChoice(selection, writeResponseLocation) {

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation)
    .setValue(selection);
}

...

html

<script>
  function onSuccess1() {
    const choice2 = document.getElementById('choice2').value;
    google.script.run
      .withSuccessHandler(onSuccess2)
      .writeChoice(choice2, "B1");
  }
  function onSuccess2() {
    google.script.host.close();
  }
  function submit1() {
    const choice1 = document.getElementById('choice1').value;
    google.script.run
      .withSuccessHandler(onSuccess1)
      .writeChoice(choice1, "A1");
  }
  
  function setup1() {
    const button = document.getElementById('submitbutton1');
    button.addEventListener("click", submit1)
  }

</script>
...

CodePudding user response：

Think I found a solution here fiddling together with a friend.

// =========================================================================
//
// EMPTY CELLS A1 and A2 before re-run
//
function start() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1:B1').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
  spreadsheet.getRange('A10').activate();

// START HTML POP-UP
dropDownModal()
};

// -------------------------------------------------------------------------

function dropDownModal() {
  var htmlDlg = HtmlService.createHtmlOutputFromFile('dropdown.html')
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setWidth(350)
    .setHeight(175);
   
  SpreadsheetApp.getUi()
    .showModalDialog(htmlDlg, 'Box title');
};

function writeChoice1(selection1) {
  const writeResponseLocation1 = "A1";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation1)
    .setValue(selection1);
}

function writeChoice2(selection2) {
  const writeResponseLocation2 = "B1";

  SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheets()[0]
    .getRange(writeResponseLocation2)
    .setValue(selection2);
}

// =========================================================================

And the

dropdown.html :

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<script>
  function onSuccess() {
    google.script.host.close();
  }

  function submit() {
    const choice1 = document.getElementById('choice1').value;
    const choice2 = document.getElementById('choice2').value;

    google.script.run
      .writeChoice1(choice1)

    google.script.run
      .withSuccessHandler(onSuccess)
      .writeChoice2(choice2)
  }
  
  function setup() {
    const button = document.getElementById('submitbutton');
    button.addEventListener("click", submit)
  }
</script>

<body onl oad="setup()">
  <p>
    Text 1.
  </p>
  <form>
    <select id="choice1">
      <option value="choice 1-A">choice 1-A</option>
      <option value="choice 1-B">choice 1-B</option>
      <option value="choice 1-C">choice 1-C</option>
    </select>
    <br><br>
    <select id="choice2">
      <option value="choice 2-A">choice 2-A</option>
      <option value="choice 2-B">choice 2-B</option>
      <option value="choice 2-C">choice 2-C</option>
    </select>
    <br><br>
    <button id="submitbutton">Submit entries</button>
  </form>
</body>

</html>

This all works.

The one thing I was hoping to understand and hopefully someone here can explain.

Why can I not

google.script.run
 .withSuccessHandler(onSuccess)
 .writeChoice1(choice1)
 .writeChoice2(choice2)

And why does even in the working version

 .withSuccessHandler(onSuccess)
 .writeChoice2(choice2)

work, but this having the the closing after writing the choice not as such:

 .writeChoice2(choice2)
 .withSuccessHandler(onSuccess)

The confusing part is that I would assume close before writing would mean no write, so i switched them around somewhere early on in testing.

Happy the code works, but still wanting to learn if someone knows :)

You have two functions with the same name writeChoice

You have two functions with the same name `writeChoice`