Return Sheets data based on URL Parameter in Google Apps Script / Web App-CodePudding

This is a piece of a larger project. Essentially, I'm going to have a link with a parameter ("formid" in this case) that I need to use to retrieve the correct row number from a Google sheets table. The code below works the way I want it to with the exception of the parameters not being used and the rows being retrieved are hard coded. I'd like to change this so the getBody row corresponds to formid number (ie.: if formid=4 then the 4th row would be displayed. Column positions can be hardcoded, I only need the one variable to be used.

Index.html:

<!DOCTYPE html>
<html>
<head>
</head>
<style>
</style>
<body>
<form>
  <h1><center>Sheet</center></h1>
</form>
<script>
  google.script.url.getLocation(function(location) {
    document.getElementById("formid").value = location.parameters.formid[0];
  });
</script>



<div>
        <table>
      <thead>
        <? const headers = getHeaders();
        for (let i = 0; i < headers.length; i  ) { ?>
          <tr>
            <? for (let j = 0; j < headers[0].length; j  ) { ?>
            <th><?= headers[i][j] ?></th>
            <? } ?>
        <? } ?>
      </thead>      
   <tbody>
      <? const body = getBody(); 
      for (let k = 0; k < body.length; k  ) { ?>
        <tr>
          <? for (let l = 0; l < body[0].length; l  ) { ?>
          <td><?= body[k][l] ?></td>
          <? } ?>
      <? } ?>     
    </tbody> 
    </table>
    </div>




</body>
</html>

Code.gs:

function doGet() {
return HtmlService
        .createTemplateFromFile('Index')
        .evaluate();      
          }



function getHeaders() {
  var url = "https://docs.google.com/spreadsheets/d/1NnG5lEKowlU6i2ZzkyCD1bjFtFGcgaODKZxvG179XfM/"; 
  const sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Sheet1");
    return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues();
}

function getBody() {
  var url = "https://docs.google.com/spreadsheets/d/1NnG5lEKowlU6i2ZzkyCD1bjFtFGcgaODKZxvG179XfM/"; 
  const sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Sheet1");
  const firstRow = 8;
  const numRows =  1;
  return sheet.getRange(firstRow, 1, numRows, sheet.getLastColumn()).getDisplayValues();
}

I've reviewed many related questions but either the solutions didn't seem to work or it wasn't clear what the full solution was. Perhaps I missed something.

I've tried inserting "formid" into the "return sheet.getRange()" but I keep getting an error that formid isn't an int.

I've made several attempts at this and the code above represents the closest and simplest script that has gotten me most of the way there.

CodePudding user response：

I believe your goal is as follows.

You want to show only a row from the Spreadsheet by giving the row number using the query parameter like formid=5.

Modification points:

In your script, it seems that you are retrieving the query parameter on the HTML side. In this case, when the template is used, after the HTML is loaded, google.script.url.getLocation is run. So, in this answer, I would like to propose retrieving the query parameter on the Google Apps Script side.
In the current stage, when the loop process is included in the HTML template, the process cost becomes high. Ref (Author: me)

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

HTML side:

In your HTML, document.getElementById("formid") is not found. So, in this modification, only HTML template without the script is used.

<!DOCTYPE html>
<html>

<head>
</head>
<style>
</style>

<body>
  <form>
    <h1>
      <center>Sheet</center>
    </h1>
  </form>
  <div>
    <table>
      <?!= table ?>
    </table>
  </div>
</body>

</html>

Google Apps Script side:

In this modification, only doGet function is used as follows.

function doGet(e) {
  const url = "https://docs.google.com/spreadsheets/d/1NnG5lEKowlU6i2ZzkyCD1bjFtFGcgaODKZxvG179XfM/";

  const { formid } = e.parameter;
  const sheet = SpreadsheetApp.openByUrl(url).getSheetByName("Sheet1");
  const [header, ...values] = sheet.getDataRange().getValues();
  const h = "<thead><tr>"   header.map(e => `<th>${e}</th>`).join("")   "</tr></thead>";
  const b = values[formid - 1] ? "<tbody><tr>"   values[formid - 1].map(e => `<td>${e}</td>`).join("")   "</tr></tbody>" : "";
  const html = HtmlService.createTemplateFromFile('i16');
  html.table = h   b;
  return html.evaluate();
}

In this modification, for example, when a Web Apps URL like https://script.google.com/macros/s/###/dev?formid=5 including the query parameter is used, formid=5 is retrieved in the doGet function, and only the row of formid=5 is shown.

Note:

When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".