user4411473 user4411473 - 2 months ago 6
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
      .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


  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
    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.

Answer

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