Search code examples
javascripthtmlgoogle-apps-scriptgoogle-sheetsweb-applications

How to obtain a specified row from a Google Sheet from doGet with JavaScript?

I'm not understanding this output, in that, as expected, the row parameter is being passed to doGet. Why is the read function only returning row #3, which is shown as A3, and not returning the expected range?

Clearly the row variable is being output to HTML correctly. Why is the range not using that value as expected?

output

html:

<!DOCTYPE html>

<html>

<head>
  <base target="_top">
</head>

<body>
  <h1>iterator</h1>
  <form action="<?= url ?>" method="GET">
    note <input type="text"  name="update" />
    <br>
    row <input type="text"  name="row" />
    <input type="submit" name="Submit" /><br> <br> <br>
    <span><?= params ?></span> <br> <br> <br>
    <span><?= update ?></span> <br> <br>
    <span><?= row  ?></span> <br> <br> <br> <br>
    <span><?= values ?></span> <br>
  </form>

</body>

</html>

Contents of Code.js:

const sheetID = "1DalaGJ3rGHmagpUqq3aOXSKRkyZQapfw-bCNTW5lgBE";
const sheetName = "data";

function doGet(e) {
  var row = 3;
  var params = JSON.stringify(e);
  var htmlOutput = HtmlService.createTemplateFromFile('index');
  htmlOutput.params = params;
  htmlOutput.update = 'updated value is: ' + e.parameter['update'];
  htmlOutput.row = 'row is: ' + e.parameter['row'];
  htmlOutput.values = read(row);
  htmlOutput.url = getUrl();
  return htmlOutput.evaluate();
}

function read(row) {
  var sheet = SpreadsheetApp.openById(sheetID);
  var data = sheet.getSheetByName(sheetName);
  var range = data.getRange(row,1,1,3);
  var values = range.getValues();
  Logger.log(values);
  return values;
}

function getUrl() {
  var url = ScriptApp.getService().getUrl();
  return url;
}

The logs show data of of:

Oct 10, 2022, 8:11:43 PM
Info
[[A3, B3, C3]]

whereas I would expect to see A1, B1, C1 instead, as contained in the spreadhseet. Why is row staying at the initial value? The odd part, is that the output to HTML would indicated the the variable is mutable.

This is towards CRUD operations on the spreadsheet. Here, looking to read a row specified by the user.

I've tried changing variable names, it doesn't seem to a scope issue.

Solution

  • read is returning only row 3 because the variable row was declared in this line var row = 3;. Between this line and the call to read(row) there is nothing changing the value assigned to the row variable.

    If you are struggling with reading code, avoid reusing the same sequences of characters like row to name multiple elements, in this case:

    • server side variable
    • HtmlTemplate property
    • Template HTML variable (used inside scriptlets)
    • URL parameter,

    Instead use unique sequences for each of them except for HtmlTemplate property and the Template HTML variable as they should use the same.