write data in google sheet using a web script app

2019-07-25 14:04发布

问题:

I am trying to do Something apparently very simple, but I don't get it : I would like to create a google web app, in wich the user write some text, and when he clicks on a button, the text goes into a Google Sheet.

Then, I would like to do also the contrairy : when the user click on a other button of the google web app, he can see what is written in a cell of the google sheet

My biggest problem is that i just don't know how to do the connexion between the webb app and the sheet, and how to use it.

Can you help me ? Thanks

回答1:

Here is a search script I modified to work. Input text press submit and the words goto Spreadsheet.

You need to deploy as web app.

CODE.GS

function doGet(e) { // main function
  var template = HtmlService.createTemplateFromFile('index.html'); 
  return template.evaluate().setTitle('Search Drive').setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

// Process the form
function processForm(searchTerm) {
  var resultToReturn;
  resultToReturn  = TextToSheet(searchTerm);
  return resultToReturn; // return the results
}

function TextToSheet(searchTerm) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(searchTerm);
 
}

INDEX.HTML

<html>
  <head>
    <base target="_top">
    <script>
      function displayMessage() {
        var searchTerm;
        searchTerm = document.getElementById('idSrchTerm').value;
        console.log('searchTerm: ' + searchTerm );
         google.script.run.withSuccessHandler(handleResults).processForm(searchTerm.replace("'","\'")); 
       }
         
        
      function handleResults(results){
         var length=results.length; // total elements of results
       }


    </script>
  </head>
  <body><center><br/>
   Input word to SpreadSheet: <input type="text" id="idSrchTerm" name="search">
    <input type="button" value="Submit" name="submitButton" onclick="displayMessage()"/>
</center>
  </body>
</html>