Question 169979 in Html Question posted in
Some basic documentation can be found here.

list Can HTML & Google Apps Script create conditional input based on data chosen in a select list?

In order to obtain the necessary code to get a function that can be executed every time a local is selected in "id-local" and that what it does is the following: with the data that this input has

<form class="row g-12 " id="facturacion-form"> 

    <div class="col-md-3 input-group-lg has-validation"> 

     <label for="id-local" class="form-label">LOCAL</label> 

     <select class="form-select" id="id-local" name="id-local" required> 

       <option selected>Elije local</option> <?!=options?> 

     </select> 

  <div class="invalid-feedback"> Por favor elige un local 
  </div> 
</div>

look in the google sheets sheet: "XXXXXXXXX", in the sheet with name "DB_LOCALES" in the range "B:O" where in "B" are the name of the premises and in "O" are the name of the companies corresponding, so that in this other input

<div class="input-group "> 

   <span class="input-group-text">Sociedad</span> 

   <input type="text" class="form-control" id="sociedad-local" placeholder="Cooland Proyect S.L" aria-label="sociedad-local" aria-describedby="sociedad-local"> 

</div>

At the moment I have the following function:

function updateSociedad() {
  
  var libroLocales = SpreadsheetApp.openById("XXXXXXXXXXX");
  var hojaLocales = libroLocales.getSheetByName("DB_LOCALES");
  var rangesoc = hojaLocales.getRange("B:O");
  
  var values = rangesoc.getValues();
  
  var local = document.getElementById("id-local").value;
  
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == local) {
      // Actualizar el valor del campo "sociedad"
      var sociedad = values[i][14]; // Columna "O"
      document.getElementById("sociedad-local").textContent = sociedad;
      break;
    }
   console.log(values);
  }
}

but if I put it in the .gs it gives me errors that it can’t access the document.getElementById but if I put it in the HTML it shows me that it can’t access the spreadsheet

My intention was to create a call to the function from another input, but it is being impossible due to my lack of experience.

I would greatly appreciate help

Tags:

2

Answers


  1. 0

    I believe your goal is as follows.

    • When you select an option of <select class="form-select" id="id-local" name="id-local" required>, you want to search a value from the column "B" of hojaLocales.getRange("B:O") using the selected value. And, when the value was found, you want to put the value of column "O" of the same row into <input type="text" class="form-control" id="sociedad-local" placeholder="Cooland Proyect S.L" aria-label="sociedad-local" aria-describedby="sociedad-local">.

    Modification points:

    • Unfortunately, I cannot know the script for giving <?!=options?>. So, in this modification, it supposes that the script for giving <?!=options?> are valid values for searching with hojaLocales.getRange("B:O"). Please be careful about this.
    • In your HTML, in order to send the selected value to Google Apps Script, it is required to communicate between the Javascript side and Google Apps Script side. This has already been mentioned in a comment.
    • In your Google Apps Script, there is no document.getElementById.
    • And, you are retrieving the values from columns "B" to "O" with var rangesoc = hojaLocales.getRange("B:O");. In this case, the values are columns "B" to "O". But, in your loop, var sociedad = values[i][14]; // Columna "O" is used. In this case, the value is trying to be retrieved from column "P".
    • In order to put a value into input tag, value is used.

    When these points are reflected in your script, how about the following simple modification?

    HTML & Javascript side:

    <form class="row g-12 " id="facturacion-form">
  <div class="col-md-3 input-group-lg has-validation">
    <label for="id-local" class="form-label">LOCAL</label>
    <select class="form-select" id="id-local" name="id-local" required onchange="sample(this);">
       <option selected>Elije local</option> <?!=options?>
     </select>
    <div class="invalid-feedback"> Por favor elige un local
    </div>
  </div>
</form>

<div class="input-group ">
  <span class="input-group-text">Sociedad</span>
  <input type="text" class="form-control" id="sociedad-local" placeholder="Cooland Proyect S.L" aria-label="sociedad-local" aria-describedby="sociedad-local">
</div>

<script>
function sample(e) {
  const value = e[e.selectedIndex].value;
  google.script.run.withSuccessHandler(sociedad => {
    document.getElementById("sociedad-local").value = sociedad;
  }).updateSociedad(value);
}
</script>

    Google Apps Script side:

    In this case, in order to search for value from column "B", TextFinder is used.

    function updateSociedad(local) {
  var libroLocales = SpreadsheetApp.openById("XXXXXXXXXXX");
  var hojaLocales = libroLocales.getSheetByName("DB_LOCALES");
  var range = hojaLocales.getRange("B1:B" + hojaLocales.getLastRow()).createTextFinder(local).matchEntireCell(true).findNext();
  return range ? range.offset(0, 13).getDisplayValue() : "";
}

    Testing:

    When the above HTML is opened as the HTML template, <?!=options?> is loaded. And, when you select one of options, the function sample is run. And, the selected value is sent to Google Apps Script side with google.script.run. And, the value is searched from the column "B". And, when the value was found, the value of column "O" is returned. At Javascript side, the returned value is used in withSuccessHandler. And, the value is put into <input type="text" class="form-control" id="sociedad-local" placeholder="Cooland Proyect S.L" aria-label="sociedad-local" aria-describedby="sociedad-local">.

    Note:

    Unfortunately, I couldn’t know the method for opening the HTML from your question. If you are using Web Apps, please be careful about the following points.

    Reference:

    Login or Signup to reply.
  2. 0

    NOTE: This script is intended to serve as a starting point or reference for your project. It’s important to note that the community members do not provide coding services.

    As previously indicated on the comments, implementation of the google.script.run class is necessary. To facilitate your project, we suggest referring to the following concise example (added a sample testing button on the html code to run the process):

    Script

    Code.gs

    function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index');//add the of the HTML file for your web app
}

/** SpreadhseetApp doesn't work non-server-side functions (inside JavaScript in HTML), 
 * it has to be processed server side .gs file.
 * */
function updateSociedad(local,sociedad){
  var libroLocales = SpreadsheetApp.openById("XXXXXXXX");
  var hojaLocales = libroLocales.getSheetByName("DB_LOCALES");
  var rangesoc = hojaLocales.getRange("B:O");
  var values = rangesoc.getValues().filter(cells => cells.join('').length != 0);

  var results = values.filter(cells => cells[0] == local && cells[13] == sociedad);

  return results;
}

    Index.html

    <!DOCTYPE html>
<html>

  <head>
    <base target="_top">
    <script>
      /**
       * RUN A SCRIPT AFTER PRESSING THE TEST BUTTON
       */
      document.addEventListener('DOMContentLoaded', () => {
        const btn = document.getElementById("btn");
        const input = document.getElementById("sociedad-local");
        const local = document.getElementById("id-local");

        btn.addEventListener("click", (e) => {
          //USE THIS google.script.run TO RUN SERVICE SIDE APPS SCRIPT FUNCTION CALLED 'updateSociedad()'
          google.script.run.withSuccessHandler(data => {
            console.log(data);
          }).updateSociedad(local.value,input.value);//Pass Local & sociedad-local values to the server side apps script
        });
      });

    </script>
  </head>

  <body>

    <form class="row g-12 " id="facturacion-form">

      <div class="col-md-3 input-group-lg has-validation">

        <label for="id-local" class="form-label">LOCAL</label>

        <select class="form-select" id="id-local" name="id-local" required>

          <option selected>Elije local</option>
          <?!=options?>

        </select>

        <div class="invalid-feedback"> Por favor elige un local
        </div>
      </div>

      <div class="input-group ">

        <span class="input-group-text">Sociedad</span>

        <input type="text" class="form-control" id="sociedad-local" placeholder="Cooland Proyect S.L" aria-label="sociedad-local" aria-describedby="sociedad-local">

      </div>

    </form>

    <button id="btn">TESTING</button>

  </body>


</html>

    DEMO

    • Sample DB_LOCALES sheet data:

    enter image description here

    • Test running the web app:

    enter image description here

    References

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search