javascript - How do you Import data from a Google Sheets?

Question

Ask a Question

Welcome To Ask or Share your Answers For Others

javascript - How do you Import data from a Google Sheets?

asked Oct 24, 2021 in Technique[技术] by 深蓝 (71.8m points)

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

268 views

1 Answer

深蓝 · Answer 1 · 2021-10-23T17:51:22+0000

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>

Categories

javascript - How do you Import data from a Google Sheets?

Please log in or register to add a comment.

Please log in or register to answer this question.

1 Answer

Example

Please log in or register to add a comment.

Just Browsing Browsing

Most popular tags