Render partial google spreadsheet on web app google apps script

user_11889830 0
Viewed 963

I've intended make google spread sheet as a shared web site using freezed row. because on a shared web site, I couldn't fix columns and rows at all. finally I noticed It may be possible to make successively if i use web app at google apps script as partial rendering.(as using html or javascript)

but I couldn't invoke partial google spread sheet which was setted range. when I did this I could see

"It couldn't be showed".

but strangely when I didn't add range, It showed well. But It's not what I want. (I got the information from here : https://www.steegle.com/google-sites/how-to/insert-websites-apps-scripts-and-gadgets/embed-google-sheet-range)

This is the applicable range that I want to fix on a website as using google apps script and web app. https://docs.google.com/spreadsheets/d/1L-NvkrbmiDk2nT7bUTRalrgON6K5zEFYZMu362GVNnE/htmlembed?single=true&gid=1680391550&range=A1:AI7&widget=false&chrome=false&headers=false

And this is all part of the document. https://docs.google.com/spreadsheets/d/e/2PACX-1vQZBVb3b0HgvmLSJBXpp57NkZTaa5Zn_HTQGwkenlhI_vLxmgxN_jwUY_TbLLCszyqIF2Ht-1qNf37o/pubhtml?gid=1680391550&single=true

I published document to be able to be showed to all user accessing the web app(viewing permission) on google spread sheet and web app.

I'd appreciate it if you could let me know about whatever you know, how to resolve this problem from A to Z. I want to know how to fix partial google spread sheet which was invoked as url on html page on web app from google apps script. or the way how to invoke partial google spread sheet on web app from google apps script. thank you.

google-apps-script google-sheets web-applications html
3 Answers

Issue:

Rows cannot be frozen on published sheets: see How can I freeze cols/rows in a public shared table?.

Workaround #1. Embed spreadsheet itself:

Instead of publishing the file to the web and embedding this published version (URL containing /pubhtml), you could embed the spreadsheet itself, if you don't have problems making this spreadsheet public (shared with Anyone with the link). This way, the spreadsheet would be seen as it is, with the frozen rows on top.

You mentioned in comments, though, that you have noticed that the frozen rows cannot be seen correctly on your mobile device, and this causes this workaround not to be appropriate for your situation.

Workaround #2. Build HTML table:

A rather less direct and more laborious workaround could be to do the following (I'll just give some broad pointers and include references for you to investigate further):

Code sample:

Here I attach the code to retrieve the data from the sheet and use it to create a dynamic table (the styling features –frozen rows, general formatting– and the issue with the merged cells are not included –if you have problems with these issues, I'd suggest you to post questions separately about these–):

Code.gs:

function doGet(e) {
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function getHeaders() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("출석부");
  return sheet.getRange(1, 1, 7, sheet.getLastColumn()).getDisplayValues();
}

function getBody() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("출석부");
  const firstRow = 8;
  const numRows = sheet.getLastRow() - firstRow + 1;
  return sheet.getRange(firstRow, 1, numRows, sheet.getLastColumn()).getDisplayValues();
}

index.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  <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>

Reference:

user_106120110

You can place it as iframe in your html and then add a lot of parameters to format it the way you want. Many resources online for the parameters.

<span><br><iframe src="DOCSURL/pubhtml?gid=37486499&amp;single=true&single=true&range=A2&amp;widget=false&amp;chrome=false&amp;headers=false" height="50" ></iframe> </span>
<br>

That basically it gives me a single cell. Besides the parameters you can apply css to your iframe.

Let me know if that helps!

user_21936370

The Apps Script HTML table rendering solution is good and well-explained with examples. However it's very slow for thousands of rows.

A way to address this may be to instead use a Javacsript library on the client that asynchronously retrieves the data and loads it into the page.

OPTION: Get a filtered CSV using the /gviz API

use a frontend/backend model where you write an HTML/JS page that calls the Google Visualization API to return only the correct rows via CSV. The URL will look like https://docs.google.com/a/google.com/spreadsheets/d/SHEET_KEY/gviz/tq?tqx=out:csv&sheet=Sheet1&tq=select%A%2C%20sum(B)%20group%20by%20A

OPTION: Download the entire sheet as CSV into the page

And filter with Javascript https://docs.google.com/spreadsheets/d/SHEET_KEY/export?format=csv&id=KEY&gid=SHEET_ID and render it.

OPTION: Use Apps Script to extract rows and return a filtered CSV

Use the Apps Script endpoint as a "CSV API" to download filtered sets of data to the page.

user_16287150
Related