0

Well, I may be being a bit too tricky here. I have a text in column B, where if it starts with 'I', I want a hyperlink. If it starts with anything else, I want the result of some time calculation. The time calculation using the ArrayFormula() is fine, no need to fix that.

The problem is the Hyperlink is showing the text (it is not showing as a hyperlink) "Create next week activities" but the CreateNextWeek() function is not firing. This is the formula in my cell.

=if(left($B22,1)="I",HYPERLINK(CreateNextWeek(G22),"Create next week activities."),ArrayFormula(if(len(F22),F22+if(len(Q22),Q22,N22)/24,)))

In case anyone need my app script code, it is simply this:

function CreateNextWeek(originDate) {
  var ss = SpreadsheetApp.getActive();
  var title = 'Function Started';
  var message='Create Next Week for after ' & originDate;
  ss.toast(message,title); 
}

I click on the cell, nothing happens. I think it has something to do with the cell needing to change from text mode to hyperlink mode and back again depending on my IF condition. Is that a possibility? Not sure how to fix that either, if it is the case. Soo, how do I make this cell behave the way I want it to work?

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Dominic Vella
  • 36
  • 5
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. In order to correctly understand your question, can you provide the sample input and output situations you expect? – Tanaike Feb 13 '23 at 02:29
  • Hi Tanaike. I'm not sure how much easier I can make it. I'll try to describe what is in each cell in row 22. $B22 is the text "ISO Week" $G22 is the date 2022/02/19. $T22 has the formula as stated above. In this scenario, a hyperlink should show in the cell T22, which when clicked, should start the Apps Script CreateNewWeek() function. So far, I see the "Create next weeks activities." text, but it's not a hyperlink and the CreateNewWeek() function is not firing. – Dominic Vella Feb 13 '23 at 04:20
  • 1
    `which when clicked, should start the Apps Script CreateNewWeek() function.` That's not how hyperlink works. `HYPERLINK` function should reference a https url. You maybe interested in a simpler workaround using checkboxes: https://stackoverflow.com/a/57842475 – TheMaster Feb 13 '23 at 04:31
  • Ahhhh, clever. Thank you for the new strategy. Only, I don't want the check box unless $B22 starts with "I", otherwise I want it hidden. Is that possible? – Dominic Vella Feb 13 '23 at 04:47
  • The syntax for [HYPERLINK](https://support.google.com/docs/answer/3093313) is `HYPERLINK(url, [link_label])`. Where in the spreadsheet, in your scenario, does the `url` exist? – Tedinoz Feb 13 '23 at 05:50
  • I thought, through reviewing some other pages, that you could use an Apps Script function instead of a URL. I have been advised that won't work. TheMaster has shown an alternative idea to start an Apps Script function, however I have a problem now of hiding a CheckBox if $B22 does not start with 'I'. – Dominic Vella Feb 13 '23 at 06:28
  • You have not described the process for your scenario, and this is essential to establishing where and when and how the script should initiate. 1) User enters text in cell B22 (no trigger yet because Column G == a date); 2) User enters a date in cell G22 (Note: no URL is entered). 3) Cell T22 contains a formula but this could just as easily be populated by the script. In any event, @TheMaster 's suggestion of a checkbox is _really_ an implementation of an `onEdit` trigger. An alternative would be to check for an edit in B22 or G22, on condition that both B22 and G22 have values. – Tedinoz Feb 13 '23 at 07:08
  • Hi Tedinoz. Nothing needs to happen unless someone clicks in the Hyperlink. It's true, the Hyperlink won't work. Hyperlink or CheckBox is circumstantial. The CheckBox is an excellent idea! But my question is concerned about the IF(). So now, I only want a CheckBox on rows where the B column starts with 'I'. Only then should the CheckBox (I've thrown the hyperlink idea in the bin) appear. If I can't control the visibility of the checkbox, I may as well create a [Create New Week] button to trigger the script. – Dominic Vella Feb 13 '23 at 07:37

1 Answers1

1

Ok, so the answer appears clear.

  1. HYPERLINK will not fire an Apps Script function.
  2. You cannot create a HYPERLINK within an IF() structure. It will appear as a text. In saying that, it is possible to use an IF() inside a Hyperlink().
  3. Using a CheckBox combined with an OnEdit() action is a very clever way to activate an Apps Script.
  4. You cannot simply hide and/or unhide a Checkbox simply with an IF(). The best you can do is hide/unhide a column using some Apps Script.

My end result is to create a button using the drawing tools and add a CreateNewWeek() function. Whilst I can't pick a week using the date in the CurrentRow, I will look down a column for the last ISO week number and build my new week based on that value. For me, sacrificing a date functionality to keep a one only button (place to fire my Apps Script) is a great compromise.

Dominic Vella
  • 36
  • 5