0

Edit / Update: I copied the exact same code into another Apps Script project and it works. Unfortunately I want to use this widget on this specific shared spreadsheet. There must be something with the configuration. Does anyone know what this might be? I don't see anything relevant in system settings and my file order and appscript.json files are the same.

I am trying to get user input from an HTML modal back into my appscript code. The modal displays a calendar where a user selects a date and hits submit. All I want to do is retrieve the date that the user clicked and run another function in appscript. I was using SpreadsheetApp.getUi().prompt() before and that worked well but I want to be a bit fancier and have a calendar display (I didn't see an option in the appscript ui library to display a calendar). If there is a better way to do this, that would be much appreciated!

Here is my HTML:

<html>
<head>
  <title>HTML</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>

  <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/themes/smoothness/jquery-ui.css"
    crossorigin="anonymous" referrerpolicy="no-referrer" />
  <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/jquery-ui.min.js" crossorigin="anonymous"
    referrerpolicy="no-referrer"></script>
  <script>
    $(function () {
                $("#datepicker").datepicker({
                    beforeShowDay: function (d) {
                        var day = d.getDay();
                        return [day != 0 && day != 2 && day != 3 && day != 4 && day != 5 && day != 6];
                    },
                });
            });
  </script>
</head>
<body align="center">
  <form id="Form" onsubmit="runThis()">
    <input type="text" id="datepicker" value="YYYY-MM-DD"/>
    <input type="submit" value="Submit">
  </form>
  <script>
    function runThis() {
      alert("Hi!");
      google.script.run.doSomething();
    }
    </script>
</body>

</html>

Here is my appscript code:

function calendar() {
  var html = HtmlService.createHtmlOutputFromFile("CalendarInput");
  SpreadsheetApp.getUi().showModalDialog(html, "Choose the Monday start date from the calendar:");
}

function doSomething() {
  getMasterSpreadsheet().toast("hello");
  console.log("woohoo!");
  return true;
}

When I hit "submit" in the HTML modal, the alert "Hi" shows up so I know it is running the function runThis() in the HTML code. But it isn't running doSomething(). I checked my execution history and doSomething() isn't there. When I run doSomething() in the console, I successfully get the toast and log. I also tried wrapping it in a success/failure handler (even though my understanding is that this is only for the case where you want to pass info from server back to client) and it fails with

NetworkError: Connection failure due to HTTP 500

I know this has been asked before but I've looked at all the other answers and nothing is working for me. Any idea why this isn't working? Thanks so much.

Improve this question
5
  • Althoguh I cannot know your whole script, in your script, when you modify getMasterSpreadsheet().toast("hello"); to SpreadsheetApp.getActive().toast("hello"); and test it again, is that your expected result? In this csea, when you click the button on the dialog, toast("hello") is run. Commented Mar 17, 2024 at 0:16
  • Ah thanks but it doesn't change anything - they refer to the same sheet. I was using SpreadsheetApp.getActive() before but I thought maybe the active state was compromised being called from the html. But regardless it seems like doSomething() isn't being run at all because there are no logs or execution history of it Commented Mar 17, 2024 at 5:28
  • and it fails every time. Could you add what the failure error is? .withFailureHandler(e => console.log(e)) Commented Mar 18, 2024 at 5:45
  • Thanks @TheMaster, the failure error is NetworkError: Connection failure due to HTTP 500. I'll update my post as well. It seems like once I'm on the HTML client side it is unable to get back to the server side. Note that .withFailureHandler(e => console.log(e)) did not produce any logs in the execution history, but I am able to see the error with .withFailureHandler(e => alert(e)) Commented Mar 19, 2024 at 2:14
  • Wow.. okay I just copied the exact same code into another project and it works. Unfortunately I want to use this widget on this specific shared spreadsheet. It must be something with the configuration. Any idea what this might be? Commented Mar 19, 2024 at 2:32

2 Answers 2

Reset to default
2

Solved! I was using the Head (development) version of my imported libraries. Deleting them, deploying them and re-adding them as a versioned deployment fixed my issue. I solved this on accident because I was having an issue creating new triggers which gave the error: Script authorization failed. Please check your pop-up blocker settings and try again, which versioned libraries also fixed. I hadn't had any problems using the HEAD of libraries before but I guess it can cause some random issues. However, I don't understand why this caused these issues. If anyone has an explanation that would be much appreciated. Thank you!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

GS:

function doSomething(obj) {
  SpreadsheetApp.getUi().alert(obj.date)
}

function launchMyDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah2'),"Dialog")
}

html: filename: ah2

<!DOCTYPE html>
<html lang="en">
<head>
    <base target="_top">
</head>
<body>
  <form>
    <input type="text" name="date" id="dp" placeholder="YYYY-MM-DD"/>
    <input type="button" value="Submit" onClick="runThis(this.parentNode);">
  </form>
  <script>
    function runThis(obj) {
      google.script.run.doSomething(obj);
    }
    </script>
</body>
</html>

You can also do it this way:

gs:

function launchMyDialog() {
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile('ah2'),"Dialog");
}

function calendar() {
  var html = HtmlService.createHtmlOutputFromFile("CalendarInput");
  SpreadsheetApp.getUi().showModalDialog(html, "Choose the Monday start date from the calendar:");
}

function doSomething(obj) {
 SpreadsheetApp.getActive().toast("hello");
  console.log("obj.date");
  return obj.date
}

html:

<!DOCTYPE html>
<html lang="en">
<head>
    <base target="_top">
</head>
<body>
  <div id="rv"></div>
  <form onsubmit="runThis(this)">
    <input type="text" id="dt" placeholder="yyyy-MM-dd" name="date"/>
    <input type="submit" value="Submit">
  </form>
  <script>
    function runThis(obj) {
      alert("Hi!");
      google.script.run.withSuccessHandler( (s) => document.getElementById("rv").innerHTML = s)
      .doSomething(obj);
    }
    </script>
</body>
</html>
Improve this answer

11 Comments

This isn't doing anything different for me - the function doSomething() still isn't running. Is it working for you? I'm thinking there might be some type of setting I need to enable but I'm not sure what that would be
What have you done to debug the problem
Show me exactly what you have now. Just post it into the bottom of your question and identify where everything is located
Actually I think the most helpful thing to suggest is that you run the code with the debugger and set breakpoints at various locations and check the state of the output to see that it is building properly.
I copy and pasted your code exactly and tried debugging. I set breakpoints at the beginning of the doSomething() function and it still isn't getting there. It runs runThis() and I see the alert "Hi!" but the call back to the server isn't executing. Is the doSomething() function running for you when you execute calendar() or launchMyDialog()?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.