Web App to Google Sheet to MS Access

Uncle Gizmo said:
Excellent, easy to follow explanation.
Click to expand...

I decided to run through the instructions in the video and create my own WebApp. I ran into a few minor changes/difficulties because of recent updates to Google App Scripts, making the video slightly out of date. I will post a new video with an update.
 
AccessBlaster said:
Maybe you will post the sample URL? :D
Click to expand...

ATH YouTube Explanation
Input From Web Page to Google Sheet - Nifty Sheets

Shared Google Sheet:-
docs.google.com

Web App

docs.google.com docs.google.com

Shared Script:-

ATH Incognito YouTube Explanation
Incognito - Input From Web Page to Google Sheet - Nifty Sheets


Code.gs:-

Code: 
function doGet(e) {
  //Log the URL parameters
  Logger.log(JSON.stringify(e));
  //Test the Logging
  Logger.log("Create HTML");
  return HtmlService.createHtmlOutputFromFile("page");
}
function userClicked(xname) {
  //URL of the Spreadsheet
  var url = "https://docs.google.com/spreadsheets/d/1nCOYb_5PFAPl7DzOIio3Yss-zlrgBIHtlcyPA3Q__YA/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  //Spreadsheet Page AWFName
  var ws = ss.getSheetByName("AWFName");
  ws.appendRow([xname, new Date()]);
  //Logger.log(xname + " >>> Clicked the Button");
}

HTML Code:- page.html
Code: 
<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>
<body>
  <h1>HELLO WORLD!!!</h1>
  <!--Setup the Textbox-->
  <label for="name">Name:</label><br>
  <input type="text" id="name" name="name"><br>
  <!--Setup the Button-->
  <button id="btn"> RUN IT!!!</button>
  <script>
    document.getElementById("btn").addEventListener("click",doStuff);
    function doStuff(){
        //Pass the name from the Textbox into a Variable "username"
        var username = document.getElementById("name").value;
        //Run the function "userClicked" in the Google Script file - Code.gs
        //and pass through the variable username
        google.script.run.userClicked(username);
        //Set the name textbox to no value ""
        document.getElementById("name").value = "";
    }
  </script>
</body>
</html>
 
Last edited:
Brilliant, @Uncle Gizmo. Thank you!

I bet this could be massaged into a robust excel type form that would rival some Access forms. It could be accessed anywhere in the world through a free URL supplied through google sheets.
 
Since I proved the code and realised it's capabilities I set about creating a better version for collecting awf members names.

The code transfers the awf name, the real name and date into the spreadsheet successfully.

The built-in Google functionality adds the variables in row order in to the spreadsheet .The first entry is the AWF name, the second entry is the real name and the third entry is the date.

I added an option selection to the HTML web page so that the user could select whether they wish to remain anonymous.

i couldn't get this to work, due to my inexperience.

I added the anonymous flag between the real username and the date stamp and expected this to be transferred into the spreadsheet in a new column, but nothing happens?

I created a simple variable containing the text "test", still no expected results.

I placed the simple variable in the other positions and it added the text test correctly in the spreadsheet, but not the anonymous flag?

Assuming that there might be some corruption or some other issue with the spreadsheet, maybe, somehow it had become bound to the old version of the code I created a new new spreadsheet and replace the the URL of the old spreadsheet with that of the new spreadsheet, ran the code and it's still placed the variables the values in the old spreadsheet!

My next test of this a problem will be to create a new Google apps script and add the information to Two new spreadsheets simultaneously, then comment out one and the then the other to see what happens. ...

Will let you know how I get on!
 
Hi uncle, is the privacy set just for you or can anyone see the table / sheet.
 
AccessBlaster said:
Hi uncle, is the privacy set just for you or can anyone see the table / sheet.
Click to expand...

I created the links and made them public, both the one for the spreadsheet and the one for the Script. The script generates a webpage for entering the data. I tested them both in incognito mode in the Google browser and they worked fine. You can see my test in this video:-

Incognito - Input From Web Page to Google Sheet - Nifty Sheets​


I suspected from your comment that you are having problems accessing them, so I have just double checked...

I logged out of my Google account to be sure that I wasn't getting access though Google, then I opened both the links directly from the Access World Forums (AWF) website, in incognito mode. They both worked fine. I'm pretty sure you should be able to access both the Google sheet showing the results, and also the script for filling the sheet.
 
Uncle Gizmo said:
My next test of this a problem will be to create a new Google apps script and add the information to Two new spreadsheets simultaneously, then comment out one and the then the other to see what happens. ...
Click to expand...

I made a simple test case for the problem I was having and after faffing about with it, I came to the conclusion that I was not deploying the project correctly. I suspect the correct procedure is to select "Deploy" and then "Test Deployments" Now I've got this test setup working correctly I should go back to the original project and experiment with that, but not tonight!
 
This is the latest version of this project.

Collect AWF Names v2​

Deploy Public
https://script.google.com/macros/s/...wTkFqTOfaP-foC6w4EQB6NbSBVxpSlsSPUk4FI2Q/exec

Unfortunately I've got to stop playing and put it aside and get on with some real work!

Below is the code used in the project, both the the the Google Apps Script and the HTML:-

Script:-
Code: 
function doGet(e) {
  Logger.log("doGet Ran");
  //Create the Data Collection Web Page
  return HtmlService.createHtmlOutputFromFile("collectnames");
}
//End function doGet(e)
//URL of the Get AWF Names Spreadsheet:- https://docs.google.com/spreadsheets/d/1jFnBZ-kqGG7UzHS-VYkYu6ssy3uD5G5N0N2zc3wXQEg/edit#gid=0
function userClicked(awfname, realname, rbutton) {
  //URL of AWF Spreadsheet
  var urlAWF = "https://docs.google.com/spreadsheets/d/1jFnBZ-kqGG7UzHS-VYkYu6ssy3uD5G5N0N2zc3wXQEg/edit#gid=0";
  var wsNameAWF = "AWFNames"
  //Open Spreadsheet - Collect AWF Names
  var ssAWF = SpreadsheetApp.openByUrl(urlAWF);
  //Open Spreadsheet Page AWFNames
  var wsAWF = ssAWF.getSheetByName(wsNameAWF);
  wsAWF.appendRow([awfname, realname, rbutton, "AWF_SS", new Date()]);
  //Test the Logging
  Logger.log("ATH Test 12345");
}
//End function userClicked(awfname, realname, test)

HTML:-
Code: 
<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    h1 {
      text-align: center;
    }
    p {
      text-align: center;
    }
    div {
      text-align: center;
    }
    form {
      text-align: center;
    }
  </style>
</head>
<body>
  <h1>Collect AWF Users Real Name!</h1>
  <p>Please enter the name you are known by on AWF - (Your Handle)</p>
  <form action="/action_page.php">
    <label for="awfname">AWF Name:</label><br>
    <input type="text" id="awfname" name="awfname"><br>
    <p>Please enter your real name.</p>
    <label for="realname">Real Name:</label><br>
    <input type="text" id="realname" name="realname"><br><br>
    <h1>Do you wish to hide your Real Name?</h1>
    <label for="incog">Hide Real Name? </label>
    <input type="radio" name="incog" value="Yes" checked="checked">Incognito</input>
    <input type="radio" name="incog" value="No">Reveal my Name</input>
  </form>
  <h1>Press the button below to save your entry to the Google Sheet</h1>
  <div class="flex-parent jc-center">
    <button id="namebtn">------ Record Names ------</button>
  </div>
  <h1>Spreadsheet HERE:-</h1>
  <p><a href="https://docs.google.com/spreadsheets/d/1jFnBZ-kqGG7UzHS-VYkYu6ssy3uD5G5N0N2zc3wXQEg/edit#gid=0" target="_blank">CLICK for
      the "Collect AWF Names" Spreadsheet</a></p>
  <script>
    //Call the Button
    document.getElementById("namebtn").addEventListener("click",doStuff);
    function doStuff(){
        //Pass the names from the Textboxes into the Variables
        var awfname = document.getElementById("awfname").value;
        var realname = document.getElementById("realname").value;
       
          //Get Value of Checked Radio Button
          var selectedRadioButton;
          //Set the constant radioButtons to the Radio Button set named:- incog
          const radioButtons = document.querySelectorAll('input[name="incog"]');
         
          //find which Radio Button in the Collection of radioButtons is Checked
          for (const radioButton of radioButtons) {
            if (radioButton.checked) {
              //Return Checked Buttons value
              selectedRadioButton = radioButton.value;
              break;
            }
          }
var temp;
temp = realname
//https://www.w3schools.com/js/js_if_else.asp
if (selectedRadioButton == "Yes") {
  temp = "Hidden";
}
        //google.script.run.userClicked(awfname, realname, "ATH Test");
        google.script.run.userClicked(awfname, temp, selectedRadioButton);
        //Set the textboxes to no value ""
        document.getElementById("awfname").value = "";
        document.getElementById("realname").value = "";
    }
  </script>
</body>
</html>
<!--END-->
 
In this video I show the steps from:- getting the data through a webpage placed into a Google sheet, save the Google sheet as an Excel file, then copy the data from Excel into Access.

It's a long winded process, but at least it works! The Excel file was a "good" :( duplicate of the Google spreadsheet in that it had multiple, unnecessary columns to the right. I removed these from the Google sheet. This removed them from later copies of the Excel file. But I still had too many rows in the Google sheet below the entries and these empty rows got copied into Excel, and then into the MS Access table, not good!

I went back to the Google spreadsheet and deleted all of the rows below the data. I tested to see if the Google Apps Script would enter the data from the webpage into the sheet, even though there wasn't a row ready to accept the data, it works fine! Automatically adding a new Row as it went.

Now I had a block containing just the data. I created a new Excel file from this new version of the Google spreadsheet. Followed the MS Access import process and was very pleased to see that it only imported the rows & columns containing data, job done!

Get Data From Google Sheet into MS Access - Nifty Access​

 
You must log in or register to reply here.

Similar threads

Uncle Gizmo
Web Scraping with Google Sheets & ChatGPT
Replies
0
Views
586
Uncle Gizmo
Uncle Gizmo
L
Google sheet as data source
Replies
6
Views
814
Uncle Gizmo
Uncle Gizmo
Uncle Gizmo
Table's in Google Sheets....
Replies
4
Views
911
Uncle Gizmo
Uncle Gizmo
Uncle Gizmo
Use ChatGPT with Google Sheets to create a Blog
Replies
6
Views
1,534
kalabas
K
Uncle Gizmo
Web Scraping with Google Sheets
Replies
0
Views
483
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom