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

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

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

For exemple, I write a value (00156610320218160021) in a blanck cell, and after this, the link it will be: =HIPERLINK("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").

The next cell, if I write this value (0012204-19.2019.8.16.0025), the link will be: =HIPERLINK("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:

Before I write this numbers, the cell needs to be blanck;
The hiperlink needs to change according the cell´s value;

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

Someone know how can I do this?

CodePudding user response：

Description

You could simply have a formula in the next cell and concatinate the contents of cell column A. Another option is to use an onEdit(e) simple trigger to replace the contents of column B.

Formula

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

Script

function onEdit(e) {
  if( e.range.getSheet().getName() === "Sheet1" ) { // Make sure we are on the right sheet
    if( e.range.getColumn() === 1 ) {  // Make sure the edit occured in column A
      var link = "https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&flagNumeroUnico=true&numeroProcesso=";
      link = link.concat(e.value,";",e.value);
      e.range.offset(0,1).setValue(link);
    }
  }
}

Reference

CodePudding user response：

Here's another Apps Script solution which will generate the links on each row of column B when you make a change on column A. Could be useful if you also need to paste a large amount of IDs on column A and want to generate them all at once.

function onEdit(e) {

  var editedrange = e.range; //gets the range that triggered the edit event

  if (editedrange.getColumn() == 1) {//only works if the change occurs on the first column
      var colvalue
      var nextcell
      var url

    for(var i = 1; i<=editedrange.getNumRows(); i  ){ //iterates through all the edited rows in the first column
      colvalue = editedrange.getCell(i,1).getValue(); //gets value of the cell at row i of column 1
      nextcell = editedrange.getCell(i,1).offset(0, 1) // gets the cell in the next column

      url = `https://projudi.tjpr.jus.br/projudi/processo/buscaProcesso.do?actionType=pesquisaSimples&flagNumeroUnico=true&numeroProcesso=${colvalue}` //builds the URL
      nextcell.setFormula(`=HYPERLINK("${url}","${colvalue}")`) //sets the URL in a formula on the next cell
    }
  }
}