Data from Google Spreadsheets on your site

Hello!

For those who use Google Spreadsheets, there is a good news - the solution that allows you to import data from a spreadsheet to your site is described below.
')
Go.

1. Open the Google Spreadsheet

For an example, I will take the table in which results from Google Forms fall.

2. Open the Tools - Script Editor menu ...

3. Add a script

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({'result': data})).setMimeType(ContentService.MimeType.JSON); } 

If necessary, override the variable “range”.

If you want to refine the script, the links to the documentation:

• Spreadsheet Service
• Class sheet
• Class range

4. Publishing as a Web Application

Open the menu Publish - Deploy as web app ...

Enter the name of the new version of the project (for example, 1).
Change the value for “ Who has access to the app ” to “ Anyone, even anonymous ”
Click “ Deploy ”.



When you first deploy, you need to go through the authorization process. I will not describe, show screenshots.


Next you will be shown a popup with a link to your web app:



You need to copy this link.

You can immediately open it in the browser. The application should return the contents of the selected table range in JSON format:



That is, any GET request to this web application you can get the data tables. It is possible from backend or frontend, depending on the needs of the business.

An example of work on the frontend

Link to l.englishdom.com/google-s.html
For ease of perception of the script, I did not make an additional design of the output information.

On the page are used:

Js

 (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("<br/>") + "<br/><hr/>"; } } catch(e) {} } document.getElementById('info').innerHTML = output; } xhr.send() })() 

HTML

  <div id="info"></div> 

Successes!

Reader Bonuses

We give free access to three months of learning English through our online courses. To do this, simply follow the link until December 31, 2017.



We will be glad to see you at the individual lessons of the course “English for IT-specialists”.
Complete a free introductory lesson and get a comprehensive feedback on your level of knowledge, then choose a teacher and a training program for yourself!