How to pull a random row from google sheets and use it in an HTML table

Question

I'm working on a small project where I am wanting to randomly pull one row of a google sheet, and then use that data in an HTML table.

For now, my solution has been first to use javascript to make a random number, then generate an HTML table from google sheets for just that row using this method. So, I end up with a URL for an HTML table with just header row, and a random row of data, similar to this. Then I just embed that table as an object in my HTML page. This is the gist of it:

< script >
  window.onload = function() {
    var albumno = Math.random() * 408;
    var albumno = Math.round(albumno);
    var albumurl = "https://docs.google.com/spreadsheets/d/UNIQUE-DOCUMENT-ID/gviz/tq?tqx=out:html&tq=SELECT%20B%2C%20D%2C%20E%2C%20F%2C%20G%20WHERE%20A%20MATCHES%20%27" + albumno + "%27&gid=1739065700";

    document.getElementById("output").innerHTML = "ALBUM NUMBER: " + albumno + ".";
    document.getElementById("albumpath").innerHTML = '<object align="middle" data="' + albumurl + '">';
  }; 
</script>

There are two major drawbacks. First, the table cannot be (easily) formatted when embedded as an object. Second, my google sheet is a list that is added to weekly, and therefore I have to manually adjust the limits of the random value generated in my javascript.

Is there a way to do this more effectively in Javascript? Perhaps by scraping the full table, and then randomly selecting a row of data, which can be used in an proper HTML table (i.e. not embedded as an object)? Or maybe there exists a google sheets API that would help me?

UPDATE:

I have managed to write a quick function in Google Apps Script that picks the random row of data. I have figured two ways to output the data, option 1 as an array, or option 2 as HTML code for a table. Now how do I call this function in my HTML page, and make use of these data?

}

function randomalbum() {
  
  //get spreadsheet
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/[DOCID]/edit#gid=1');
  var sh = ss.getSheetByName("Pick List");
  
  //find last album
  var Fvals = sh.getRange("F:F").getValues();
  var Flast = Fvals.filter(String).length;
  var colnum = sh.getRange(2, 1, Flast).getValues();  
  var albumlast = Math.max.apply(null, colnum)-1;
  
  //pick random row (note the row an album is in is 1 more than the album no.)
  var rowrand = Math.round(Math.random()*(albumlast+1));
  
  //extract data of interest
  var albumrand = rowrand -1;
  var pick = sh.getRange(rowrand, 5).getValues();
  var artist = sh.getRange(rowrand, 6).getValues();
  var title = sh.getRange(rowrand, 7).getValues();
  
  //make array (option 1)
  var array = [albumrand, pick, artist, title];
  
  return array;
  
  //make HTML string (option 2)  
  var HTMLString = " <table style='width:100%'>"
                   + "<tr>"
                     + "<th>Album No.</th>"
                     + "<th>Picked By</th>"
                     + "<th>Artist</th>"
                     + "<th>Artist</th>"
                   + "</tr>"
                   + "<tr>"
                     + "<td>" + albumrand + "</td>"
                     + "<td>" + pick + "</td>"
                     + "<td>" + artist + "</td>"
                     + "<td>" + title + "</td>"
                    + "</tr>"
                    +"</table>"
  
   HTMLOutput = HtmlService.createHtmlOutput(HTMLString);
 
  return HTMLOutput
  
}

Have you considered publishing a web app via Apps Script? There are several ways of getting data from a spreadsheet and using it in an HTML, inclusing directly using the Sheets API, but I don't know what tools are you using (or consider using). I would need more details on your case. — Iamblichus
I don't understand your first drawback: the table be formatted when embedded as an object. You mean you don't know how to embed the object to the HTML table? The second of your drawbacks could be solved, for example, by first making a call to the API to retrieve the number of currently existing rows in the sheet, and using that retrieved value to get the random row index. — Iamblichus
That should have read 'the table cannot be formatted when embedded as an object'. I'll check out the Sheets API. I honestly know very little about this stuff. Would you happen to have any resources that would show examples of how to use the API to retrieve data, then use it in HTML? Linking the two is the step I'm not clear on. — codenewb
Thats depends on how are you calling the API. If you were publishing a web app in Apps Script, you wouldn't even need to call the API directly. And you could use printing scriplets to embed the data from the sheet in a dynamic (and formatted) table. And there are other ways to embed this data in the HTML. See, for example, EJS. Probably the easiest way is publishing a web app, if you're up to it. I could provide sample code if you're up to using this. — Iamblichus
I have made a google script that pulls the data and makes it an array (see original post). So... now I'm needing to figure out how I get the data from the script onto my html page. — codenewb

nexus nexus · Accepted Answer · 2020-01-16T22:31:40

Using a Google Sheets API without needing to run your own server: I remember following the suggestions in the following article to solve this a while ago.

https://coderwall.com/p/duapqq/use-a-google-spreadsheet-as-your-json-backend

You'll probably need another service to add CORS headers to the Google response because cors.io apparently no longer exists.

How to pull a random row from google sheets and use it in an HTML table

2 Answers