user4411473 user4411473 - 1 year ago 89
Javascript Question

Return every value in spreadsheet to web app table

What I'm trying to do:

  1. Get every value from a spreadsheet (6 columns, ~10+ rows)

    • I can get the values. I will probably get each column individually with
      .getRange(row, column, sheet.getLastRow() - 1, 1).getValues();
      . Or I could get every single value with
      and then put them in the table that way?

    • Or would it be better to just use a for loop? -- example at very bottom

  2. Return those values [as Templated HTML?] surrounded by
    <table>, <tr>, <th>, <td>
    code -- example below

  3. Display the table with the values on a web app when a name gets

    • Can the inserted
      code start as
      style="display: none;"
      , and then when the name gets clicked, have the table show? Or have it in a div that is invisible then visible once clicked?


Example of table:



I dont know if what I'm trying to do counts as Templated HTML or not, or if it would be better to do this using Templated Html...

The big thing I need help with is returning every value in a table and displaying it on the web app.



Example of for loop

If I do a for loop and return every value in a column or a row




How would I return those values as a table?

Would it be something like:

var x = "<tr>";
var y = row1[i];
var z = "</tr>";

return x + y + z

If so, how would I make a whole table out of all the row/column values?

Also, how would I style the table with css once it's fully built? I'm assuming I can still add an id to the table and style it from there.

I don't know the exact number of rows there will be in the spreadsheet, so making this dynamic would be best -- probably getting all the rows instead of columns and using a for loop for each row?

Any help would be appreciated. Thanks.



If I wanted to add another column to the table (not in the spreadsheet), how would I go about doing that? I'm trying to add an add row button to the header row, and a delete row button on the end of every row of table data.

Also, I know that you can set the
<td contenteditable='true'>
... but, how would I have it where the data is only editable on a newly added row AND the table cell cant be edited once a value is submitted? -- Would I need an event listener or some trigger that sees when a value is inputted and triggers that cell to

Thanks again.

Answer Source

You can use a nested for loop.

var range = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues();
var isHeader = true;
var html = "<table>\n";
for (var i = 0; i < range.length; i++) {
  html += "<tr>\n";
  for (var j = 0; j < range[i].length; j++) {
    if (isHeader) {
      html += "<th>" + range[i][j] + "</th>\n";
    else {
      html += "<td>" + range[i][j] + "</td>\n";
  isHeader = false;
  html += "</tr>\n";
html += "</table>";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download