Html view google sheet

How to Display Google Sheet in Web App

In this post, I demonstrate how to display a Google Sheet Table in a Web App using Google Apps Script.

How to Video:

Video Notes:

• Legacy Apps Script Editor Used in Video
• Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
• For Further Details on Deploying a Web App Click Here
• Templated HTML Documentation
• Sample Web App From Video

Code in Video:

function doGet(e) < var htmlOutput = HtmlService.createTemplateFromFile('DisplaySheet'); return htmlOutput.evaluate(); >function getSheetData()

    
Display Google Sheet Web App
    ?> else < ?>    ?> ?> ?>  

Related Posts

How to Create Web App Report from Google Sheets — In this post, I demonstrate how to take a Web App and display an HTML report with input filters using Google Sheets. Also included in this video is the use of the QUERY formula for Google Sheets.

Convert Google Sheet into JSON Web App — In this post, I demonstrate how to convert a Google Sheet into a JSON Web App. The JSON Web App can be used as a web service to bring data into a website.

How to Sort Google Sheet on Google Web App — In this post, I demonstrate how to Sort a Google Sheet on a Google Web Application. It is set up for each Column to be Sorted in Ascending Order.

How to Filter Google Sheet in Google Web App — In this post, I demonstrate how to Filter a Google Sheet in a Google Web Application using HTML and Google Apps Script.

Источник

Данные из Google Таблиц на вашем сайте

Для тех, кто пользуется Google Таблицами, есть хорошая новость — ниже описано решение, которое позволит импортировать данные из таблицы на ваш сайт.

1. Открываем Таблицу Google

Для примера, я возьму таблицу, в которую падают результаты из Google Формы.

2. Открываем меню Tools — Script Editor…

3. Добавляем скрипт

var ss = SpreadsheetApp.getActiveSpreadsheet(), // spreadsheet s = ss.getActiveSheet(); // sheet function getData() < var result = [], range = 'A:E', // диапазон ячеек, который хотим выгружать values = s.getRange(range).getValues(), last_row = parseInt(s.getLastRow()); for (var i = 1; i < last_row; i++) < result.push(values[i]); >return result; > function doGet() < var data = getData(); if(!data) < data = ''; >return ContentService.createTextOutput( JSON.stringify()).setMimeType(ContentService.MimeType.JSON); > 

При необходимости переопределите переменную “range”.

Если вы захотите доработать скрипт, ссылки на документацию:

4. Публикация как Web Application

Открываем меню Publish — Deploy as web app…

Вводим название новой версии проекта (например 1).
Меняем значение для “Who has access to the app” на “Anyone, even anonymous”
Нажимаем “Deploy”.

При первом деплое, нужно пройти процесс авторизации. Описывать не буду, покажу скриншотами.

Далее вам будет показан попап с ссылкой на ваш web app:

Вам нужно скопировать эту ссылку.

Можете сразу открыть её в браузере. Приложение должно вернуть содержание выбранного диапазона таблицы в формате JSON:

То есть любым GET-запросом к этому веб-приложению вы можете получить данные Таблицы. Можно с бекенда или фронтенда, в зависимости от потребностей бизнеса.

Пример работы на фронтенде

Ссылка на l.englishdom.com/google-s.html
Для простоты восприятия скрипта, я не стал делать дополнительное оформление выводимой информации.

На странице используются:

(function () < var app = "https://script.google.com/macros/s/AKfycbzngb-5a3tDdHJ445o5LxqeN63V_ihhF_Nxlkz3gxLyMeG3MA/exec", output = '', xhr = new XMLHttpRequest(); xhr.open('GET', app); xhr.onreadystatechange = function() < if (xhr.readyState !== 4) return; if (xhr.status == 200) < try < var r = JSON.parse(xhr.responseText), result = r["result"]; for (var i = 0; i < result.length; i++)< var obj = r["result"][i]; output += obj.join("
") + "
"; > > catch(e) <> > document.getElementById('info').innerHTML = output; > xhr.send() >)() 

Бонусы для читателей

Мы дарим бесплатный доступ на три месяца изучения английского с помощью наших онлайн-курсов. Для этого просто перейдите по ссылке до 31 декабря 2017 года.

Будем рады видеть вас на индивидуальных занятиях курса «Английский для IT-специалистов».
Пройдите бесплатный вводный урок и получите комплексную обратную связь по своему уровню знаний, затем выбирайте преподавателя и программу обучения себе по душе!

Источник

How to pull data from Google Sheets to HTML table

You can build several types of Web Apps using Google Sheets Apps Script. In previous posts, we discussed creating several types of online data entry forms using Google Apps Scripts. This post will show you how to pull data from Google Sheets to an HTML table and display it in a Web app created with Google Apps Script.

With this method, you can create beautiful tables, and you can allow visitors to search and sort data in your HTML table. This way, you can show your data to others without giving access to your Google Sheet.

All the above technologies are free to use. We use Bootstrap to style the Web App. The jQuery and DataTables plugin is used to add search, sort, and filter functions to the HTML table.

Table of Contents

Pull data from Google Sheets to HTML table

Using this tutorial, you will create a web app, as shown in the following video. The data in the table is imported from a Google Sheet.

Step 02 : Create a new Apps Script project / Make a copy

We use Google Apps Script to pull data from Google Sheets to an HTML table. There are two ways to use Google Apps Script. The first is Container-bound Scripts, and the second is Standalone Scripts. Here I am using the second, so it does not bound the script to the Google Sheet. So, you need to create the App Script Project separately, and you cannot open it using the “Tools” menu of the Google Sheet.

Below, I explain how to create a new Standalone Google Apps Script. Alternatively, you can copy the Google Apps Script project in this example with the complete code from the following link. Then you can skip this section and jump to Step 03.

To create a new Standalone Google Apps Script project,

• Go to Google Drive
• Click the “New” button on the top left corner and go to “More” and click “Google Apps Script“.

Then copy and paste the following code snippets.

Important !: If you are creating the project with a new Google Apps Script (instead of making a copy of the Apps Script project given above), you need to enable Google Sheets API Service for your project.

• Click the + icon in the Services tab,
• Select Google Sheets API from the list in the Add a service popup box.
• Then, click Add.

Code Snippets

Code.gs

This file includes the server-side functions.

To work the following code for your Google Sheet, you need to change the variables “spreadSheetId” and “dataRange” in lines 9 and 10 to match your Google Sheet. You can identify your Google Sheets ID as explained here.

In the above Code.gs file, the function doGet() tells the script how to serve the page and returns the HtmlOutput object. You must include this function in your script to create a web app with the HTML service.

The function getData() returns the data range as an array.

The function include() , use to include the JavaScript.html file inside the Index.html file. Using this function, I have included the codes in the JavaScript.html file in the Index.html file. You can learn more about this in HTML Service: Best Practices in Google Apps Script guide.

JavaScript.html

I have separated the client-side JavaScript codes into this JavaScript.html file. It is then included in the Index.html file using the include() function in the Code.gs file.

The following JavaScript function calls the getData() function in the Code.gs file during the page load. Then, the returned data array and passed it to the showData() function. Then it generates the data table and inserts it into the page.

Index.html

This file includes the HTML and JavaScript required to generate the HTML page. The JavaScript file mentioned above is included in the header section using include() function as shown below in line 12.

The data table is printed inside the table tag in line 19.

Step 03 : Deploy as a Web App

To create the HTML page, you need to deploy your project as a Web app.

• Click the blue color “Deploy” button
• Click “New deployment“
• In the New deployment pop-up window,
  • For Description, give a meaningful name so that you can later identify this version.
  • Select your email for “Execute as” and then the web app will not require users to run the code using their account data.
  • Select “Anyone” for the “Who has access” section so that anyone on the internet can access this link.

  Watch the video below that demonstrates the above steps.

  

  DataTable functionalities

  • Search data – You can use the search box to search data in any column of the data table.
  • Sort Data – Click the table headers to sort data in ascending or descending order.
  • Pagination – By default, this table shows 10 entries per page. Click the page numbers below the table to view the other entries.
  • Change number of entries per page – Change the number of entries per page from the “Show entries” dropdown.

  Display URLs as hyperlinks

  If your Google Sheet has a column with URLs, the above code shows them as simple text values. In order to show the URLs as hyperlinks, you can modify the JavaScript.html file as below.

  Let’s say you are adding a new column with URLs to the end of the data table (column G of the Google Sheet).
  Add the following code next to line 20 of the JavaScript.html file.
  (Do not forget to update the dataRange in the Code.gs file according to new changes.)

  If you want to show a custom text instead of the URL you can use the following code.

  Did you know that it’s possible to showcase your Google Sheets data on HTML charts? Absolutely! By utilizing Google Apps Script and the Google Charts JavaScript library, you can effortlessly create visually appealing and interactive dashboards for your Google Sheets data. Read the following blog post to read more.

  

  Wrapping Up

  In this example, I showed you how to pull data from Google Sheets to an HTML table. I created the HTML table in a Web app created with Google Apps Script. Google Apps Script is completely free, and you can build many types of Web apps. I used the Bootstrap CSS library to style the HTML table. And the JQuery DataTable plugin was used to add more functionalities such as search, sort, and pagination to the HTML table.

  You can share this web app with others using its URL. The visitors can get updates to the Google Sheet by refreshing the web app. Using this method, you can easily share your Google Sheets data with others without giving access to the entire spreadsheet. You can also Embed Google Apps Script Web Apps in Websites.

  Share this:

  Источник