Skip to content
Advertisement

How to make a hyperlink function that changes according to cell value?

I want to put a value into the cell and get a research hyperlink.

I’m using the link: https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&flagNumeroUnico=true&numeroProcesso=

For example, I write a value (00156610320218160021) in a blank cell, and after this, the link it will be:

=HYPERLINK("https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&flagNumeroUnico=true&numeroProcesso=0015661-03.2021.8.16.0021";"0015661-03.2021.8.16.0021")

enter image description here

enter image description here

The next cell, if I write this value (0012204-19.2019.8.16.0025), the link will be: =HYPERLINK("https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&flagNumeroUnico=true&numeroProcesso=0012204-19.2019.8.16.0025";"0012204-19.2019.8.16.0025")

Important things:

  1. Before I write this numbers, the cell needs to be blank;

  2. The hyperlink needs to change according the cell´s value;

Basically I want put a value into each cell in column A, and get these different links.

Someone know how can I do this?

Advertisement

Answer

I managed to solve part of the problem this way:

function hyperlink(){
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var a1 = activeSheet.getSelection().getActiveRange().getA1Notation();
  //var a1 = activeSheet.getActiveCell().getA1Notation();
  var values = activeSheet.getRange(a1).getValues();
  const link = "https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&flagNumeroUnico=true&numeroProcesso="
  var hyperVal= values.map(row=> row.map(col=> `=HYPERLINK("${link}${col}";"${col}")` ));
  activeSheet.getRange(a1).setValues(hyperVal);
  activeSheet.getRange(a1).setShowHyperlink(true); /* I initially just tried doing this, but without setting the HYPERLINK formula, it did nothing */
}

The solution is for cases where the cell is selected.

I wanted to write the value in the cell and automatically see the link. Unfortunately, I can’t find a solution for this.

Advertisement