How do you Import data from a Google Sheets?

Question

How to take the first cell of a Google spreadsheet and output it as a string with JavaScript to an HTML document?

I have an HTML document labeled: "Website.html"

<!DOCTYPE html>
<html>
     <head>
        <script>
          var textDisplay = Some code to import spreadsheet cell text;
          document.getElementById("display").innerHTML = textDisplay;
        </script>
     </head>
    <body>
        <p id="display"></p>
    </body>
</html>

and another file in the same folder labeled: "Spreadsheet.xcel" (or whatever file type that is). The first cell contains the text: "Hello".

How do I make the text in the spreadsheet import into the JavaScript of the HTML document?

Easiest would be something like `="var text = "'" & A1 & "';"` — Tim Williams, May 10 '13 at 16:10
How? Meaning I have a javascript code in an html doc called "Website" in a folder. In the same folder I have another file called "spreadsheet.excel" or whatever file type that is. How do I import it — CTOverton, May 10 '13 at 19:38
You need to update your question with more detail if you really want usable answers. What server-side platform you're using would be a good start, but don't limit yourself to just that... — Tim Williams, May 10 '13 at 19:41
Ok I will update it and please take a look. I thank you for your help! — CTOverton, May 10 '13 at 20:16
Still lacking needed details. Where are these files - on your local machine/on a web server/somewhere else? What event triggers this extraction? What language/platform do you want to use to do this? — Tim Williams, May 10 '13 at 21:43

score 16 · Accepted Answer · edited May 23 '17 at 10:30

Your solution will depend on your data source; you included google-spreadsheet, so here's an answer about that. Just stating the obvious to start: A google spreadsheet would not be a file on your server, instead it would be in "the cloud" in a Google Drive.

You can retrieve contents of google spreadsheets from your javascript, and there are examples here in SO:

Basic idea for retrieving one cell of data as a string:

In Google Drive, create your spreadsheet.
Publish the spreadsheet, picking the range A1 for the first cell. You should be provided a URL like:

https://docs.google.com/spreadsheet/pub?key=SPREADSHEET_KEY&single=true&gid=0&range=A1&output=csv

Example

function loadData() {
  var url="https://docs.google.com/spreadsheet/pub?key=p_aHW5nOrj0VO2ZHTRRtqTQ&single=true&gid=0&range=A1&output=csv";
  xmlhttp=new XMLHttpRequest();
  xmlhttp.onreadystatechange = function() {
    if(xmlhttp.readyState == 4 && xmlhttp.status==200){
      document.getElementById("display").innerHTML = xmlhttp.responseText;
    }
  };
  xmlhttp.open("GET",url,true);
  xmlhttp.send(null);
}

<html>
  <body>
    <button type="button" onclick="loadData()">Load Spreadsheet Data</button>
    <div id="display"></div>
  </body>
</html>

You can also see this as a jsfiddle here.

Thanks to GPSVisualizer, who were kind enough to publish a public google spreadsheet I could use for this example.

@ayjay - just use a different range in the URL. The range needs to be contiguous. For A1:B2, say, you'd use `var url="https://docs.google.com/spreadsheet/pub?key=p_aHW5nOrj0VO2ZHTRRtqTQ&single=true&gid=0&range=A1:B2&output=csv";`. — Mogsdad, Dec 09 '14 at 03:26

Trajano Roberto · Answer 2 · 2021-01-06T02:15:01.743

A: how to get the following code to work? html page loads google sheets page, but no data shows up?

a/ google sheet files shared with the public

https://docs.google.com/spreadsheets/d/1HVmBfKjQiUyXOfy-q5iWVvDYOSKnMLPiDr18W2EtU9s/edit?usp=sharing

https://docs.google.com/spreadsheets/d/e/2PACX-1vQ3ZHpAYDBhjSelXk-GFuFJACQzsqlufZ0d5UCLw8iJNJwdHglY7388fYHL4632wgXDIfgnrd238Htg/pubhtml

b/ html and javascript code

    <!DOCTYPE html>
<html>
<body>

<h2>Using the amstras XMLHttpRequest Object</h2>

<div id="demo">
<button type="button" onclick="loadXMLDoc()">Load Data</button>
</div>

<script>
function loadXMLDoc() {
  var xhttp = new XMLHttpRequest();
  xhttp.onreadystatechange = function() {
    if (this.readyState == 4 && this.status == 200) {
      document.getElementById("demo").innerHTML =
      this.responseText;
    }
  };
  xhttp.open("GET", "https://docs.google.com/spreadsheets/d/e/2PACX-1vQ3ZHpAYDBhjSelXk-GFuFJACQzsqlufZ0d5UCLw8iJNJwdHglY7388fYHL4632wgXDIfgnrd238Htg/pubhtml", true);
  xhttp.send();
}
</script>

</body>
</html>

I think your issue lies in the url. Notice how if you use the url from the answer it directly downloads a csv file? This is because of the `output=csv` flag. Try adding that flag as well as making your spreadsheet available public on the internet. Otherwise your code won't be able to access it. Hope this helps :) — CTOverton, Jan 05 '21 at 20:53
@CTOverton still no success. have tried code from following URL: https://www.w3schools.com/xml/xml_http.asp _ sadly, the google sheet file does not load, and I do not know why??? thanks for any help have — Trajano Roberto, Jan 06 '21 at 02:10

How do you Import data from a Google Sheets?

2 Answers2

Example

Linked