Get spreadsheet ID through HTML-CodePudding

I'm trying to get the customer to enter the ID of their spreadsheet, so that the remaining script performs the proper functions on the customer's spreadsheet. My code is currently like this:

Script.gs

var id;
var spreadsheet=SpreadsheetApp.openById(id);
function myfunction(x) {
    id=x;
}

function doGet() {
  var template = HtmlService.createTemplateFromFile('HTML')
  var html=template.evaluate();
  return html
}

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <input>ID></input>
    <button onclick="passId()">PASS</button>
    <script>

     function passId() {
      google.script.run.myfunction(id);
     }   

    </script>
  </body>
</html>

When I try to view the HTML, it returns an error saying that the value of the ID variable is invalid. The intention is for the client to inform the ID of his worksheet, as the rest of the code depends on the ID of the worksheet to work. If I assign the id manually through .gs, the HTML works perfectly in the worksheet with the id assigned, thus applying all the functions present in my code.

CodePudding user response：

In Google Apps Script, stuff set out of any function, the global scope, is executed every time that a server side function, including doGet, is ran from the editor, a user interface, including a web application, or by simple/installable triggers.

So first thing that you should do is to remove the following lines from the global scope.

var id;
var spreadsheet=SpreadsheetApp.openById(id);

Below is a simplified implementation of a web app that pass a value entered in a web application created using Google Apps Script to the server side code. Please checkout the comments.

You might try this code or adapt it to your current code.

/**
 * Creates the web application using an .html file
 */
function doGet(e){
  return HtmlService.createHtmlOutputFromFile('index');
}
/**
 * Function called from the client-side. It logs the value 
 * entered by the web-app user.
 */
function logUserInput(id){
  console.log(id);
  return `${id} successfully logged`;
}

index.html Contains the web application structure and client-side JavaScript

<!DOCTYPE html>
<html>
  <head>
  </head>
  <body>
    <label for="my-input">Enter the spreadsheet id</label><br>
    <input id="my-input" type="text"><br>
    <button onclick="sendToServer">Save</button>
    <script>
      /**
       * Called by the button click event.
       * Sends the input value to the server side and logs the server side
       * returned string or the error message if it occurs on the server side
       */
      function sendToServer(){
        /** Retrieves the input value */
        const id = document.querySelector('input').value;
        /** Send the input value to the server */
        google.script.run
        .withSuccessHandler(console.log)
        .withFailureHandler(console.error)
        .logUserInput(id);
      }
    </script>
  </body>
</html>

References

CodePudding user response：

Try this:

gs:

 function myfunction(id) {
   Logger.log(id);
   const ss = SpreadsheetApp.openById(id);
 }

 function launchmydialog() {
   SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutputFromFile("ah1"),'TEST')
 }

html:

 <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <input type="text" id="theid" placeholder="Enter the id" />
    <input type="button" value="PASS" onclick="passId()" />
    <script>

     function passId() {
       let id = document.getElementById("theid").value
      google.script.run.myfunction(id);
     }   

    </script>
  </body>
</html>

I usually set these things up as a dialog first just to get them running.