Execute as Link (in addition to Open as Link)

Ask for Help
1

UPDATE: I have become aware that there is a flaw with this as running a script that alters the spreadsheet needs permissions that I can’t seem to figure out. The below app works fine when I’m logged in to my Google account in a browser, but if not, it does not. I’ll update this when or if I figure out a way.

I realize this may be an advanced feature but…

I have written a few Google script web apps that act as an API that return JSON formatted data from a spreadsheet. In any event it occurred to me that a developer could write a simple web app that would execute a function of their choice and just return a results flag in JSON that the Glide app would display, similar to the check that appears with a Form is submitted. So instead of opening up the devices default browser the link would be launched internally, without shelling out to a browser and the resultant JSON would be parsed for the success or fail message. Or empty message for that matter which would not show any return message within the app.

I have frequently seen requests in the community here of people asking for a way to run a spreadsheet function from within Glide, or have seen posts where if that feature were available it would solve their issue/request.

Here is a demo app I wrote to show this: https://randomize.glideapp.io (it is copyable)
Here is a share link to the spreadsheet (because I believe the script will not come across when you clone the app): Random Sort Demo - Google Sheets

It’s a very simple app just to demonstrate the ability to run a function by the “Open as Link” option of a button. All it does is run a function that updates a column that is used to sort the data. I did implement the sort on a separate sheet so you can see the native data and the sorted data, but it could also have been done with the Glide sort feature.

If the “Execute as Link” were implemented then you would not see the browser open at all, just the success message. There still is the data refresh delay inherent with Glide only polling the sheet for changes every few min.

Getting the web app working is another whole topic that is too long to post here.

2 Likes
2

Here is the Google Script in case someone can’t get it.

function doGet(request) {
  var response;

  try {
    var sheetName = request.parameter.sheetname;
    var columnNumber = request.parameter.colnumber;
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var lastRow = sheet.getLastRow();
    var range = sheet.getRange(2,columnNumber,lastRow-1,1);
    var data = range.getValues();
    var newData = randomNumberList(sheetName, columnNumber)
  
    range.setValues(newData);
    
    response = {Result : "Success"};
  } catch(e) {
      response = {Result : e,
                  QueryString: request.queryString};
  }
  
  return ContentService.createTextOutput(JSON.stringify(response));
}

function test_randomNumberList() {
  var data = randomNumberList("Questions", 5);
}

function randomNumberList(sheetName, colNumber) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var randRange = lastRow * 10;
  
  var data = [];
  
  for (var i = 1; i < lastRow; i++) {
    data.push([Math.floor(Math.random() * randRange) + 1]); 
  }

  return data;
}
3

When I click on the button, I get a request for permission prompt, so if you see a request from me, that’s why. I do like your idea of “Execute as Link”. Really opens things up if you are looking to perform an api call without expecting a result back. I’ve thought about building some smart home integration into an app, but haven’t pursued it yet. I have some lights that can be controlled on or off by simply calling a URL. Some interesting ideas though.

image
image843×300 18.7 KB

4

Yeah I just saw that request. I sent you an email regarding that. The spreadsheet is set up to share with all as view only. I thought you would be able to just copy the sheet and the script would come with it. If I go into the script and look at the share setting it also says it is shared with anyone with a link. Interesting. Can you copy the spreadsheet or does this message prevent you from doing that? Here is the share link when I run File/Share from the script. https://script.google.com/d/1FDLOznBaNB4wYEwxJ2MKysr4C5r2f5_pOLWJ34YOA0QOjfDXfqv7xeCD/edit?usp=sharing

2462×1332 283 KB

5

I can see your sheet and the script just fine. I get the permission screen when I click on the Randomize button within your app. I suppose it’s macro protection. Since you set it up, only you have permission to execute the URL.

6

Well that’s a bummer. I’ll have to see what I can do to work around that. There is an option when publishing the script to deploy it as a API executable but I had issues and just when with the web app method I was familiar with. When I get a chance I’ll look at that route. @Jeff_Hager If you come up with any ideas or get it to work let me know.

1394×852 111 KB

1 Like
7

I have made a copy of your app, sheet and script. If I get a chance to play with it more, I’ll be sure to let you know if I find out more. Still a good starting point though.

8

This post is old and Glide has implemented a randomize solution so it is pointless except to demonstrate a concept. The app is still there but I have changed the action to just copy the link to the clipboard instead of running it.

1 Like