Using a web app to execute a container bound script?

[Mike]

I have a number of spreadsheets with lots of automation scripts and menus to execute them. I am trying to find a way to run some of these scripts using my iPhone. I was able to get the web app to increment a count when I access the URL on my iPhone - so that’s good.

Now I am trying to figure out how to use this approach to execute a specific function inside the spreadsheet. Is it possible to use a Web App to execute a container-bound function? If yes, what does that look like in code (how do I call the function)? Advice appreciated!

Web App looks like this so far:
function doGet(e) {
if(e != null) {
var ss = SpreadsheetApp.openById([spreadsheet ID]);
var sheet = ss.getSheetByName("CounterTest");
var count = sheet.getRange("A1").getValue()+1;
sheet.getRange("A1").setValue(count);
}

return ContentService.createTextOutput("I got your counter request and the count is now "+count);
}

[Alan Wells]

Does this StackOverflow post answer your question?

https://stackoverflow.com/questions/30126787/call-a-custom-gas-function-from-external-url

[Mike]

Thanks Alan!  

This goes a long way to answering my question.  I though I had to add a standalone web app to do this, but just tried it inside the container bound script (as you indicated), and it works great!  

Now I just have to read and understand your use of the search string parameter to be able to execute a specific function inside the container bound script.  Not so clear yet to me, but hopefully I can figure it out from your example. More to learn, but thanks for sharing and giving me a head start.

I might be able to execute that function from my iPhone after all!  

[Mike]

One last question (I hope)...

What is the correct way to return a variable from the function I am calling back to the doGet(e) function? In this case I want to use the value of the count variable from the IncrementCounter function, in the .createTextOutput return (eg: “I got your counter request and the count is now "+count)

// MY CURRENT CODE
function doGet(e) {
var passedString,whatToReturn;
passedString = e.parameter.searchStringName;
if (passedString === 'IncrementCounter') {
whatToReturn = IncrementCounter(); //Run function IncrementCounter
};
//return ContentService.createTextOutput(whatToReturn);
//return ContentService.createTextOutput("I got your counter request and the count is now "+count);
return ContentService.createTextOutput("I got your request and updated the counter");
};



function IncrementCounter(){
var ss = SpreadsheetApp.openById([my spreadsheet ID]);

[Alan Wells]

You need a return statement in your IncrementCounter() function:

function IncrementCounter(){
   var ss = SpreadsheetApp.openById([my spreadsheet ID]);
   var sheet = ss.getSheetByName("CounterTest
");
   var count = sheet.getRange("A1").getValue()+1;
   sheet.getRange("A1").setValue(count);

  return count;

 }

Is that what you mean?

[Mike]

That's definitely the idea.  I tried just adding "return count;" in the function, but still cannot use it property in the doGet.  Get an error "ReferenceError: "count" is not defined. (line 22, file "Code", project "WebAppTest")".  

It's probably something I am not understanding.  Very much appreciate the help...

current code:

function doGet(e) {
  var passedString,whatToReturn;
  passedString = e.parameter.searchStringName;
  if (passedString === 'IncrementCounter') {
    whatToReturn = IncrementCounter();  //Run function IncrementCounter
  };

  return ContentService.createTextOutput("I got your counter request and the count is now "+count);
  //return ContentService.createTextOutput("I got your request and updated the counter");
};


function IncrementCounter(){
  var ss = SpreadsheetApp.openById([spreadsheet ID); 

[Alan Wells]

You need to republish the web app if you made a change and are using the "exec" version of the code.  From the code you've displayed, I don't see a reason for "count" to be undefined. 

When I get unexpected results and errors that I don't understand, I just start debugging line by line until I get to the line that fails.  And it happens constantly that I get unexpected results and errors that I can hardly believe would be possible.

[Mike]

Thanks for the guidance Alan.  Good to know the approach makes sense.  I will try line by line to figure it out (but I don't think I can log the doGet).   Your help is appreciated.

[Alan Wells]

Good point about the problem with logging in the doGet() function.  I think that you can log to StackDriver from doGet.  That's what I'd try.

[Clark Lind]

I think I see the error... in your code, you assign count to "whatToReturn" not to Count. so this line should read:
 

return ContentService.createTextOutput("I got your counter request and the count is now "+whatToReturn);

[Steve Webster]

When using the mobile app for Sheets, there is another hack that works.

1. No doGet or doPost needed.

2. Create a dropdown within a sheet cell. The dropdown value is the function name.

3. Create a another function to handle onEdits and manually create the onEdit trigger to invoke that function

4. When that function executes from the on-edit, you should make sure the intend sheet tab is used if that matters.

5. Then do an eval(of-dropdown-value-the-function-you-want-to-run)

Kind Regards,

Steve Webster

SW gApps LLC, President 

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster and Remove Blank Rows

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/0acc1a1a-a5c3-4e06-ac1f-1b5fd98caa17%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mike]

I think I see the error... in your code, you assign count to "whatToReturn" not to Count. so this line should read:
 
return ContentService.createTextOutput("I got your counter request and the count is now "+whatToReturn);

Thanks!!!  Silly right?  I will check that and see.  Appreciated.

[Mike]

Thanks Steve! It was good to learn about web apps and variable passing (good practice), but your “hack” is very interesting... I will try this out. Seems like a great fit for my use case. Just trying to fix the fact that the iPhone gSheet app does not allow use of custom menus - which is how I run the scripts in the desktop browser. Love the idea of leveraging the fact that drops downs work and i can just create a sheet for that purpose. Thanks for the hint! (and to all above for the web app help). Great to have many options to learn :-)