Display a sheet in HTML Service from Google Sheets

[sup...@oralpath.ca]

Hi everyone, you all have been so helpful in this group, I thought I would now reach out to see if any one of you can help me with this project.

I have a client that is using Google Sheets as a database (i know but it works for him). I want to be able to show the data from the sheets on an HTML page (within Google Apps). I use to be able to do this using uiApp and flextables (but its now depreciated). Looking at various examples on StackOverflow, I am not sure if this can be done anymore (because google keeps changing things). For security reasons, we can not have users accessing the sheet.

Table headers:

ApptSts, Appt Date, Appt Time, CustName, CustPhone, CustEmail, CustNotes

All the data is stored and filtered in the sheet, so all I need to do is have a script pull the data from the queried sheet and display it on an HTML page (within Google Apps). Note: I am very new to using HTML Service with Apps Script, so any help/guidance would be greatly appreciated. The client is not interested in using Google Cloud (as it is too costly).

Thanks and looking forward to reading your responses and completing this project. 

Deryk

[Kim Nilsson]

I do that in my CB_LookUp web app.
Check the array building in the js, and then the display code in the html.

With it I create a table with the headers given, and the table body is from the array.

[Darren D'Mello]

I would have used a query formula in a different sheet and the publish the query sheet as html.

This will be faster then ever.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/d4a3e2f1-eaa2-4f73-bc03-95b491f78ca8n%40googlegroups.com.

[Kim Nilsson]

Darren, is your comment about my web app, or a suggestion to Deryk?

If it's about my CB_LookUp, how would you push the query to the Sheet from the web app?

I'd really love if I could make the search faster. Especially for organisations much larger than mine, with hundreds of thousands of devices.

I do run a query, but using the "library" in gvizQuery.gs, so on the array of data, and not directly in the Sheet.

/Kim

onsdag 28 oktober 2020 kl. 15:14:28 UTC+1:

[Darren D'Mello]

Sorry I misunderstood. I thought data is loaded from the sheets.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/92ff4a23-d1b5-441a-a2fc-4848f43c6b28n%40googlegroups.com.

[Kim Nilsson]

In my case I do load data from a Sheet, but not all data, only the result of my query.

But the user never sees the Sheet, so the query must be run remotely.

In Deryk's case, he too loads data from a Sheet, afaik.

Kim

[Oralpath Support Desk]

Just following up to a post I made a week ago,

Has anyone come up with a solution on how I can create a table using data from Google Sheets without using Google Cloud in HTML/gscript?

As mentioned before, I use to be able to do this using flex tables but this is no longer possible (as uiApp is deprecated).

Any help would be appreciated. Thanks everyone.

Deryk

---

This message and any attachments are intended only for the addressee(s) named in this message. This message is intended to be and to remain confidential. If you are not the intended recipient, please immediately contact us by phone at 800-401-5328 ext. 104 or you can email us at sup...@oralpath.ca. You must then delete this message. Thank You.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/131d11df-9620-4e42-bcbe-9d589d5a699an%40googlegroups.com.

---

This message and any attachments are intended only for the addressee(s) named in this message. This message is intended to be and to remain confidential. If you are not the intended recipient, please immediately contact us by phone at 800-401-5328 ext. 104 or you can email us at sup...@oralpath.ca. You must then delete this message. Thank You.

[Adam Morris]

I would highly recommend using a front end library such as:

https://datatables.net

You can set it up so that it calls server side appscript that returns results. 

I made a thing with it... will have to dig that up. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CACm51WsRESzf30orvr9OCJ%3DRo4fnrsmdYLWUBkO3g9X-xqXBtQ%40mail.gmail.com.

--

http://classroomtechtools.com

[Clark Lind]

There are a few options you could use. 
1) Create the html table using the built-in templating (scriptlets) ability:  e.g.,  <?!= some html table code ?>  within the html file itself (messy, but works).
2) Parse the data and build the table yourself into a long string, then pass it to the html file.
3) Use an external library like Adam suggests (requires a learning curve to understand it, but produces great results)
4) Probably other methods I'm not thinking of.

1 and 2 are essentially the same, and really only differ on method and where the code runs (in your script, or in your html file). Here is a video that shows mostly how to do what you want.

#3 produces great results if you are willing to put in the time to understand how the library works. I'm not a programmer, so it was a little difficult for me, but I did get datatables to work.