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

Question

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")

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?

Answer 1

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.

Answer 2

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

• https://developers.google.com/apps-script/guides/triggers/
• https://developers.google.com/apps-script/guides/triggers/events
• https://developers.google.com/apps-script/reference/spreadsheet/range#offset(Integer,Integer)

Answer 3

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
    }
  }
}