1. Read and Write from Google sheet

Here’s a quick tutorial showing how a web app can talk to a Google sheet – read and write from it.

Here’s a video of me following this tutorial.

Here’s an example of the code working:

  1. Open your Google Drive
  2. Create a new sheet by choosing New > Google Sheets.
  3. Save the sheet as babara
  4. In Google Sheets choose Tools >  Script editor…
  5. Copy/paste this code into the editor (replace the default code in the editor):
function doGet() {
  return HtmlService.createHtmlOutputFromFile('page');
}
 
function sendToSpreadsheet(data) {
 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.insertRows(1);
  sheet.getRange(1,1).setValue(data); 

  var list = "";
  for(var i=1; i<10; i++){
    list+= sheet.getRange(i,1).getValue() + "<br>";
  } 
  return list;
}
  1. Press Ctrl+S to save
  2. You’ll be asked what to call your script, call it bob.
  3. While still in the script editor, make a new HTML file: choose File > New > HTML file. Call it page. Warning, upper case/lower case matter here, unlike with normal HTML files.
  4. Copy/paste the following code into your new HTML  file (replace the default HTML code in the editor):
<html> 
<head>
	<head>
    	<style>
        	textarea{
            	resize: none;
            	white-space: nowrap;
				overflow:hidden; 
                height: 1em;
    			width: 10em;
                vertical-align: middle;
          	}
            button{
                vertical-align: middle;
            }
        </style>    
        <script>
            function send(){
                var data = document.getElementById("data").value;
                //sendToSpreadsheet is in Code.gs and getFromSpreadsheet(fromSheet) is below
                google.script.run.withSuccessHandler(getFromSpreadsheet).sendToSpreadsheet(data);
            }
 
            function sendEnter(event){
                if(event.which == 13 || event.keyCode == 13) {
                    send();
                    event.preventDefault();
                }
            }
 
            //since sendToSpreadsheet(data) in Code.gs returns data you can include argument "fromSheet" to recieve that data
            function getFromSpreadsheet(fromSheet){ 
                document.getElementById('Message').innerHTML = fromSheet;
            };
        </script> 
    </head>
<body>
    <textarea id="data" onkeypress="return sendEnter(event)"></textarea>
    <button type="button" onClick="send();">send</button>
    <div id="Message"></div>
</body>
</html>
  1. Choose Publish > Deploy as web app…
  2. In the Deploy as web app options choose
    1. Execute the app as: User accessing the web app.
    2. Who has access to the app: Anyone.
  1. Press Deploy.
  2. Copy/paste the Current web app URL into a new tab your browser.
  3. You will be then asked a number of times if you really want to run the app.
    1. Choose REVIEW PERMISSIONS in the first window.
    2. Then choose the account you want to sign in with in the second window.
    3. Then choose advanced in the third window.
    4. Then choose Go to bob (unsafe) in same window.
    5. Then choose Allow in the fourth window.
  4. Try it out. It is saving to a sheet called babara on your Google Drive and reading it back on the HTML page.