-1

I'm looking for an easy way to let my code turn a link from regular static text into a hyperlink that can be clicked. The code should let you put in an input of text, and then the code will automatically turn it into a link that lets you use that image search in the Google doodle game, Image Breakout, a spin-off of Atari Breakout.

The first thing I did was try this:

function IMAGEBREAKOUT(input) 
{
var array = [];
var url = 'https://www.google.com/search?q=' + input + '&tbm=isch&tbs=boee:1';
  array.push([url]);
  return array;
}

That only gave me regular text, and did not give me a clickable link, so then I tried this:

function IMAGEBREAKOUT(input) 
{
  var array = [];
  var url = 'https://www.google.com/search?q=' + input + '&tbm=isch&tbs=boee:1';
    array.push("<a href=url>" + url + "</a>");
    return array;
}

But, that only gave me static text with HTML formatting that didn't work. So, then I tried researching, and went on to try this:

function IMAGEBREAKOUT(input) 
{
 var array = [];
 var url = 'https://www.google.com/search?q=' + input + '&tbm=isch&tbs=boee:1';
    SpreadsheetApp.getActiveSheet().getActiveCell().setFormula('=HYPERLINK("' + url + )');
}

But it said I didn't have permission to call setFormula.

I tried authorizing the app later, and it worked when running it in the editor, but running the formula in Google Sheets gives me the same error.

I'm in a tight spot here. Can someone help me, or revise my script so that it doesn't require so much? If that's not possible, is there a simpler way to do this that I'm not thinking about? Thanks!

JBMagination
  • 13
  • 5
  • A custom function (e.g. `A1: =MyAppsScriptFunction(someInput)`) cannot modify any cell - it can only `return` a value or an array of values. This is by design. https://developers.google.com/apps-script/guides/sheets/functions#return_values – tehhowch Sep 25 '18 at 14:42
  • @tehhowch Okay, is it possible to make it return as a hyperlink? – JBMagination Sep 25 '18 at 14:55
  • @I'-'I I'm trying to get what you describe, and for me, my first function does not do that. It just gives me static text that I can't click. – JBMagination Sep 25 '18 at 15:00
  • @I'-'I I just tried `=IMAGEBREAKOUT("cute puppies")` and it doesn't work. Then I try `=IMAGEBREAKOUT("Atari")` and it's suddenly working. Now I'm confused. Your hyperlink function is doing it properly with both examples, but I need to figure out how to make it work by itself without another function. – JBMagination Sep 25 '18 at 15:12
  • @I'-'I Oh, thank you! – JBMagination Sep 25 '18 at 15:22

1 Answers1

1

Thanks to @I'-'I, I was able to find out that I needed to encode the URI, so my code now looks like this:

function IMAGEBREAKOUT(input) 
{
  var url = 'https://www.google.com/search?q=' + input + '&tbm=isch&tbs=boee:1';
  return encodeURI([url]);
}
JBMagination
  • 13
  • 5