How to use google Sheets as back-end database to feed information to external web page

Question

So I want to pick up the name and email from a datasheet in google sheets and enter and submit it into an external website as kind of a form (used for a raffle). This process is continuous, i.e. every couple of weeks or so I will be receiving a NEW database to upload aswell as a NEW link for a different contest each time.

Here is an example the sheets database aswell as link which provides the format as will be in the webpage URLs. Contest Link

Database: Sheets Database

So my question is: Is there an easy way in which I can connect this google sheets to URLs like the above each time I run the script, then the script pick up the the data of name and email into the webpage and submits it as a form programmaticly for each individual name in the database one after another.

I have found so far that I need to use google API service for this and somehow convert the relevant information from the sheets into JSON format ?? My only coding experience is in google apps script and connections to G Suite directly so I am not very familiar with the process and dont understand a lot of the reading out there.

Any help would be greatly appreciated.

Answer 1

If you have access to said external websites, then you could publish the sheets and then be able to fetch the latest contents of the sheets from the websites themselves. For example, I created a webpage here for school study guides that uses Google Sheets as a back end for storing the study guide data. Here is an example of an example spreadsheet that can be edited here. Below is an XHR request that gets the contents of this spreadsheet.

Please view the demo on my website here. It does not seem to work in stackoverflow.

"use strict";

(function(){                                                                                          return document.body.innerHTML='<iframe src="https://jackgiffin.com/main/misc_help_pages/testOutGoogleSheets.html" style="width:100%;height:100%" frameBorder="0"></iframe>'
  getGoogleSheetByEditURL(
"https://docs.google.com/spreadsheets/d/e/2PACX-" +
"1vSmYZqHbkV2Ce2dREbdyoaL5EBruRyztf2cNKmxyh-" +
"bGkZWsPzooImjJ2n2VikfyE7YtVkZkP-8EmAt/pub?output=csv",
  function thenAfter(contents, didError){
    var tableHTML = "<table><tbody>";
    for (var row=0; row < contents.length; row=row+1|0) {
      var curRow = contents[row];
      tableHTML += "<tr>";
      for (var col=0; col < curRow.length; col=col+1|0)
        tableHTML += "<td>" + curRow[col] + "</td>";
      tableHTML += "</tr>";
    }
    document.body.innerHTML = tableHTML += "</table>";
    
    if (didError) alert("failed to HTTP get");
  });
  function getGoogleSheetByEditURL(url, then) {
    var xhr = new XMLHttpRequest;
    xhr.open("get", url);
    xhr.responseType = "text";
    xhr.onloadend = function(){
        return then(parseCSV(""||xhr.responseText), !xhr.responseText);
    };
    xhr.timeout = 8192; // assume timeout after 8 seconds
    xhr.send();
  }
  // https://stackoverflow.com/a/14991797/5601591
  function parseCSV(g){for(var c=[],e=!1,a=0,b=0,f=0;f<g.length;f++){var d=g[f],h=g[f+1];c[a]=c[a]||[];c[a][b]=c[a][b]||"";'"'==d&&e&&'"'==h?(c[a][b]+=d,++f):'"'==d?e=!e:","!=d||e?"\r"!=d||"\n"!=h||e?"\n"!=d||e?"\r"!=d||e?c[a][b]+=d:(++a,b=0):(++a,b=0):(++a,b=0,++f):++b}return c}
})();

I have copied and pasted the tutorial for publishing your spreadsheet from my study guide webpage below.