Last active
February 17, 2025 02:55
-
-
Save bpwebs/d1af73aded06d81936ceca4149cf799f to your computer and use it in GitHub Desktop.
How to pull data from Google Sheets to HTML table - bpwebs.com
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| How to pull data from Google Sheets to HTML table |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| //https://www.bpwebs.com/pull-data-from-google-sheets-to-html-table/ | |
| function doGet() { | |
| return HtmlService.createTemplateFromFile('Index').evaluate(); | |
| } | |
| //GET DATA FROM GOOGLE SHEET AND RETURN AS AN ARRAY | |
| function getData(){ | |
| var spreadSheetId = "1tMODRuz4T5MYVOGtdLV5j5EqX1MKoz4F_RySpr0YLdE"; //CHANGE | |
| var dataRange = "Data!A2:F"; //CHANGE | |
| var range = Sheets.Spreadsheets.Values.get(spreadSheetId,dataRange); | |
| var values = range.values; | |
| return values; | |
| } | |
| //INCLUDE JAVASCRIPT AND CSS FILES | |
| function include(filename) { | |
| return HtmlService.createHtmlOutputFromFile(filename) | |
| .getContent(); | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <base target="_top"> | |
| <!--INCLUDE REQUIRED EXTERNAL JAVASCRIPT AND CSS LIBRARIES--> | |
| <script src="https://code.jquery.com/jquery-3.5.1.js"></script> | |
| <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script> | |
| <script src="https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js"></script> | |
| <link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css"> | |
| <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css"> | |
| <?!= include('JavaScript'); ?> <!--INCLUDE JavaScript.html FILE--> | |
| </head> | |
| <body> | |
| <div class="container"> | |
| <br> | |
| <div class="row"> | |
| <table id="data-table" class="table table-striped table-sm table-hover table-bordered"> | |
| <!-- TABLE DATA IS ADDED BY THE showData() JAVASCRIPT FUNCTION ABOVE --> | |
| </table> | |
| </div> | |
| </div> | |
| </body> | |
| </html> |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <script> | |
| /* | |
| *THIS FUNCTION CALLS THE getData() FUNCTION IN THE Code.gs FILE, | |
| *AND PASS RETURNED DATA TO showData() FUNCTION | |
| */ | |
| google.script.run.withSuccessHandler(showData).getData(); | |
| //THIS FUNCTION GENERATE THE DATA TABLE FROM THE DATA ARRAY | |
| function showData(dataArray){ | |
| $(document).ready(function(){ | |
| $('#data-table').DataTable({ | |
| data: dataArray, | |
| //CHANGE THE TABLE HEADINGS BELOW TO MATCH WITH YOUR SELECTED DATA RANGE | |
| columns: [ | |
| {"title":"Rating"}, | |
| {"title":"Reviews"}, | |
| {"title":"Book title"}, | |
| {"title":"Number of Pages"}, | |
| {"title":"Type"}, | |
| {"title":"Price"} | |
| ] | |
| }); | |
| }); | |
| } | |
| </script> |
Hi, thank you so much, I have a quick question, which file's name did you use for the 'filename' you use in line 19 & 20 in Code.gs?
worked
Help me
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
side