Import Google Sheets cell data into simple HTML elements

Question

First post here.

I'm looking to import cell data from Google Sheets into HTML elements. An example would be having a Google Sheet with item pricing where I could push the individual cell data into an HTML element.

I found these posts and it's what I'm looking for but I don't see a clear answer but it's most likely my lack of knowledge. If someone could point me in the right direction that would be great. I'm not asking for a tutorial, just an expert's opinion so I don't end up researching something thats incorrect and waste a lot of time.

Thank You.

Pull data from a google sheet and display as simple html text

RSSHow can I pull just ONE cell of data at a time for Google Sheets to embed into my HTML content?

It's unclear what exactly you are trying to do, you can create a web app that will display the cell's contents, that's possible. — Robin Gertenbach, Mar 10 '17 at 08:13
Is this an apps script webapp or calling the script from another webpage? — Spencer Easton, Mar 10 '17 at 14:35

ReyAnthonyRenacia · Accepted Answer · 2017-04-17T10:24:00.850

If you're going to use Sheets API then here's what it may look like.

I'll just give you a snippet to show this is doable.

First fetch the data from the spreadsheet using the Reading a single range guide.

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheet_id/values/range

which I used in my XMLHttpRequest:

function fetchValues(){
         console.log("-----checkIfEmpty-----");
         var xhr = new XMLHttpRequest();
         xhr.open('GET', 'https://sheets.googleapis.com/v4/spreadsheets/'+myspreadsheetId+'/values/'+"Sheet1!"+A+":"+Z);
         xhr.setRequestHeader('Authorization', 'Bearer ' + myAccessToken);

          var arrayBuffer;
          var myArr;

         xhr.onload = function (oEvent) {
             arrayBuffer = xhr.response; // Note: not oReq.responseText
             console.log("arrayBuffer "+ arrayBuffer);
             myArr = JSON.parse(arrayBuffer);

              for(var i = 0; i <= myArr.values.length; i++){
                      document.write(myArr.values[0][i] + "   ");
              }
         };
         xhr.send(null);
      }

Print it on the html page using document.write. I know this is a shabby job and there are proper ways of displaying data on the html page but I just wanted to show it's doable- from spreadhsheet to HTML page.

Import Google Sheets cell data into simple HTML elements

1 Answers1