menu

Questions & Answers

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

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.

Comments:
2023-01-18 00:10:03
I have to apologize for my poor English skill. Unfortunately, I cannot understand 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.. Can I ask you about the detail of your goal?
2023-01-18 00:10:03
Not a problem, thanks for the help. If I have a URL like this: script.google.com/home/projects/SCRIPTIDHERE/dev?formid=5 I would want to display data on the page from row # 5 on the spreadsheet The goal is to return data from only the row specified in the parameter. I hope that helps, let me know if I can clarify further
2023-01-18 00:10:03
Thank you for replying. I would like to support you. But, I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of a solution. I would be grateful if you can forgive my poor English skill.
2023-01-18 00:10:03
I appreciate you trying to help. I need to pass the parameter in the url ("5" in my previous comment) to the get.Range function as the row number.
2023-01-18 00:10:04
Thank you for replying. You want to control the values using the query parameter of formid=5. Is my understanding correct? If my understanding is correct, when formid=5 is used, you want to retrieve only row 5?
2023-01-18 00:10:04
Although I'm not sure whether I could correctly understand your expected result, I proposed a modified script as an answer. Please confirm it. If I misunderstood your question and that was not useful, I apologize.
Answers(1) :

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: