How to create a Link that is a Get Request to a Google Web App

Question

I have a button on a spreadsheet that I want users to click that will run a script. The script modifies some protected ranges so I published it as a web app and set it to run as me.

I found these instructions explaining what I am trying to accomplish:

You can create a function with the doGet() reserved function name in your project, and publish the project as a Web App. A link is a GET request. When you click the link, a GET request will be made to the published URL of the Web App. Then the doGet() function will run. The GET request is the "event" and the doGet() function is triggered by the GET request event. doGet() "listens" for the GET request to be made. You can pass information to the doGet(e) function by adding an "event" parameter in the parenthesis. Typically the letter "e" is used, but any letter can be used

My questions are once I have the web app url, how do I create a link that goes to the web app and runs it? Right now I am using a button on the spreadsheet and assigning a script to it. Screenshot of this: https://imgur.com/OeMlczb But this is not working.

And here is the code I turned into a web app that need to be called and ran when the button is clicked:

function confirm(){
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('This will submit the timesheet. Do you want to continue?', ui.ButtonSet.YES_NO);
  if(response == ui.Button.NO) return;
  emailGoogleSpreadsheetAsPDF();
}

/* Email Google Spreadsheet as PDF */
function emailGoogleSpreadsheetAsPDF() {

  // Send the PDF of the spreadsheet to this email address
  var email = "[email protected]"; 
  var exclA=['Timesheet','Note','Settings','Data'];//and others
  var timeS=SpreadsheetApp.getActive().getSheetByName('Timesheet')
  var ss=SpreadsheetApp.getActive();
  var name=ss.getRange("Timesheet!J6").getValue();//trimmed the range down to match the getValue();
  var tname=ss.getRange("Timesheet!J6").getValue();
  var agency=ss.getRange("Timesheet!B4").getValue();//same here
  var fldr=DriveApp.getFolderById('abc123thu8h7r8888tbgyru');
  var fA=[];
  var today=Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy");
  var subject=Utilities.formatString('%s has Submitted Their Timesheet and Notes',name); 
  var body=Utilities.formatString('This was submitted on %s',today);
  var shts=ss.getSheets();

  SpreadsheetApp.flush();//this may not be necessary...not sure
  var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_timesheet_notes.pdf', tname,agency,today));
  fA.push(file)

  for(var i=0;i<shts.length;i++) {
    var sh=shts[i];
    var name=sh.getName();
    if(exclA.indexOf(name)==-1) {
      sh.showSheet();
      for(var j=0;j<shts.length;j++) {
        if(shts[j].getName()!=name) {
          shts[j].hideSheet();
        }
      }
      SpreadsheetApp.flush();//this may not be necessary...not sure
      var file=fldr.createFile(ss.getBlob().getAs('application/pdf')).setName(Utilities.formatString('%s_%s_%s_note.pdf', name,agency,today));
      fA.push(file);
    }
  }
  for(var i=0;i<shts.length;i++) {
    if(exclA.indexOf(shts[i].getName())==-1) {
      shts[i].showSheet();
    }
  }
  timeS.showSheet();

  GmailApp.sendEmail(email,subject,body, {attachments:fA});
  for(var i=0;i<fA.length;i++) {
    fA[i].setTrashed(true); 
  }
  //CopyDataToNewFile();
}

function makeCopy() {
  var ss =SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Note');
  var dSheet = sheet.copyTo(SpreadsheetApp.openById(ss.getId()))
  dSheet.showSheet()
};

function CopyDataToNewFile(targetSheetName,targetSsId, sourceSheetName,sourceSsId) {
  var ss = SpreadsheetApp.openById('gnu84uw84nwutnst9ntrgbrn').getSheetByName('Timesheet');
  var ssd = SpreadsheetApp.openById('h3487g8bg8ybw4gy8wytb').getSheetByName('Sheet1');

  var therapist = ss.getRange('J6').getValues();
  var thedate = ss.getRange('A10').getValues();
  var theagency = ss.getRange('B4:C4').getValues();
  var thepayperiod = ss.getRange('B6:C6').getValues();
  var thecost = ss.getRange('E24').getValues();
  var themileage = ss.getRange('E27').getValues();

  ssd.getRange(ssd.getLastRow()+1,1,therapist.length,therapist[0].length).setValues(therapist);
  ssd.getRange(ssd.getLastRow()+0,2,thedate.length,thedate[0].length).setValues(thedate);
  ssd.getRange(ssd.getLastRow()+0,3,theagency.length,theagency[0].length).setValues(theagency);
  ssd.getRange(ssd.getLastRow()+0,4,thepayperiod.length,thepayperiod[0].length).setValues(thepayperiod);
  ssd.getRange(ssd.getLastRow()+0,5,thecost.length,thecost[0].length).setValues(thecost);
  ssd.getRange(ssd.getLastRow()+0,6,themileage.length,themileage[0].length).setValues(themileage);

}

I am just unsure how to structure this and any help is greatly appreciated, thank you!

Cooper Cooper · Accepted Answer · 2019-06-03T16:44:41

A Link to a Webapp is just a bookmark in my browser

Here's a simple example of a webapp that I just put together for you.

html:(This is the user interface) It's all done in html and so there is a wide latitude of techniques that can be deployed here depending upon your knowledge of html, javascript, css etc.....

But this one just has one text box and one button and I didn't use any JQuery or other libraries. It's just basic old school JavaScript and html.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
    window.onload=function() {
      document.getElementById('hdg1').innerHTML="You are now connected to the server";

    };
    function runMyFunction() {
      var txt=document.getElementById('txt1').value;
      if(!txt) {
        alert("You have not entered any text");
      }else{
        google.script.run
        .withSuccessHandler(function(msg){
          document.getElementById('msg1').innerHTML=msg;
        })

        .myFunction(txt);
      }

    }
    console.log('aq4.html');
    </script>
  </head>
    <body>
    <h1 id="hdg1"></h1>
    <input type="text" id="txt1" placeholder="Put some text here" />
    <input type="button" value="myfunction" onclick="runMyFunction()" />
    <div id="msg1"></div>

  </body>
</html>

Google Script:

function myFunction(txt) {
  return "<h1>I have received your message and ran myFunction():</h1><strong>" + txt + "</strong>";
}

function doGet() {
  return HtmlService.createHtmlOutputFromFile('aq4');
}

The link that I was referring to is the simply the url of the web app and all you have to do is bookmark that url and then click on the bookmark from your browser. I describe that a little in this video

How to create a Link that is a Get Request to a Google Web App

2 Answers