1

I have a google sheet with a column of information with hyperlinks. The hyperlinks work if a user clicks on them.

Scripts are not getting the links - very few of them can be accessed by a google script that uses getFormula(), getFormulas(), getFormulaR1C1, or hyperlink.

I've cut the column and re-pasted it, I've edited links. Some start working, most others don't. 

label = sheet.getRange(j,3).getValue()
url = sheet.getRange(j,3).getFormula();

Any ideas on what I need to do to either the data or the script to get them to play nicely?

Sarah K
  • 363
  • 2
  • 15
  • Although I'm not sure whether I could understand your situation, for example, is this thread useful for your situation? https://stackoverflow.com/q/53863752/7108653 If I misunderstood your question, I apologize. At that time, can you provide a sample Spreadsheet to correctly understand your issue? – Tanaike Feb 01 '19 at 01:28
  • Hi @Tanaike that's pretty much the same but the solution for that user hasn't worked for me. I have a dummy sheet at https://docs.google.com/spreadsheets/d/1tZdF27Gd2bPLNJwTb8TwBAEZy5pty3PM-F2-4JrjuYM/edit?usp=sharing I can't share the actual data that I'm working with but this replicates nicely. – Sarah K Feb 01 '19 at 01:54
  • I apologize that my comment was not useful for your situation. I think that I cannot correctly understand your situation. So can I ask you about the detail information of your issue? – Tanaike Feb 01 '19 at 02:10
  • We have a list of names and links that have been copied from an intranet and we have a team capturing specific info relating to those names. We want to output a json file of some of those and their links. It doesn't have to be json, that's not the problem. The problem is the links. The script runs successfully down the list, gets the names but isn't always able to get the urls. In my test you I've put the result, name, and link into the json feed. – Sarah K Feb 01 '19 at 02:15
  • When your shared spreadsheet is used, in the case of the cell "C1", you want to retrieve both ``Tweets getting deleted`` and ``https://forums.digitalpoint.com/threads/tweets-getting-deleted.2854682/unread``. Is my understanding correct? – Tanaike Feb 01 '19 at 02:21
  • That's right, if anything the url is more important – Sarah K Feb 01 '19 at 02:37
  • Thank you for replying. I think that the answer of [this thread](https://stackoverflow.com/q/53863752/7108653) can be used for your situation. But your comment that ``the solution for that user hasn't worked for me.``. About this situation, can I ask you about the detail information of issue? – Tanaike Feb 01 '19 at 02:50
  • Hi, it doesn't work with plain scripting and it's inappropriate to turn on the api in this instance. I'll add hyperlink to my example. – Sarah K Feb 01 '19 at 04:37
  • I couldn't notice that although I thought that the thread is useful for your situation, you had used the script without modifying for your situation. I apologize for this. When you use the script, please modify it to your situation. But you don't want to use Sheets API. Is this correct? And I couldn't understand about ``I'll add hyperlink to my example.``. – Tanaike Feb 01 '19 at 05:30
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/187760/discussion-between-sarah-king-and-tanaike). – Sarah K Feb 01 '19 at 20:00

1 Answers1

0

I've ended up going through each entry in the sheet and changing the values from plain text to a hyperlink formula. There are 400 lines so that's not much fun but we'll be using the output every day so it's worth it.

Sarah K
  • 363
  • 2
  • 15