Return every value in spreadsheet to web app table

Question

What I'm trying to do:

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 .getDataRange().getValues() and then put them in the table that way?
- Or would it be better to just use a for loop? -- example at very bottom
Return those values [as Templated HTML?] surrounded by <table>, <tr>, <th>, <td> code -- example below
Display the table with the values on a web app when a name gets clicked
- Can the inserted <table> 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:

<table>
 <tr>
  <th>header1</th>
  <th>header2</th>
  <th>header3</th>
  <th>header4</th>
  <th>header5</th>
  <th>header6</th>
 </tr>
 <tr>
  <td>value1</td>
  <td>value2</td>
  <td>value3</td>
  <td>value4</td>
  <td>value5</td>
  <td>value6</td>
 </tr>
 //etc
</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

column1[i]  
column2[i]  
etc

or

row1[i]  
row2[i]  
etc

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> to <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 contenteditable='false'?

Thanks again.

johnnyaug johnnyaug · Accepted Answer · 2016-09-16T08:38:48

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>";

Return every value in spreadsheet to web app table

1 Answers