Cells with a hyperlink to populate destination sheet with adjacent row data in google sheets (script)

Question

The code below adds a link to the cells in column A and this link, when clicked on, brings the user to cell E7, in another sheet. The column that now has the links set also has two adjacent columns (B and F) and whose row data should populate cells B19 and E7 in the destination sheet, when the user clicks on the link, on a specific row.

function addHyperlink(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataRng = ss.getSheetByName("Painel de Embarques").getRange(27, 1, ss.getLastRow(), 4).getValues();
  var rngValues = [];//This is for getting only non-empty rows
  for (var n = 0; n<dataRng.length; n++){
    if (dataRng[n][1] != ''){
      rngValues.push(dataRng);
    }
  }
  var destSheet = ss.getSheetByName("Atualizar Embarque").getSheetId();
  for (var a = 1; a < rngValues.length; a++) {
    if (rngValues[a][1] != ''){
      const fat = rngValues[a][1];
      const stat = rngValues[a][5];
      ss.getSheetByName("Painel de Embarques").getRange(27,1,rngValues.length,1).setValue('=hyperlink("https:sheetLink/edit#gid='+destSheet+'&range=E7";"Editar")');
    }
  }
  
}

The question is: How can I the script also populate destination cells like the following, when the user clicks on it? Row data column B would go on cell B19 on the destination sheet; Row data column F would go on cell E7 on the destination sheet.

Thank you!

Answer 1

You can implement a workaround with the new onSelectionChange() trigger

It is not straightforward, but for example:

• If you click on a cell in "Painel de Embarques" with the value "Editar" - you can save the correspondant row number into Script properties
• When you click on the link you get redirected to a different cell - this will also trigger onSelectionChange and you can retrieve the corresponding event range as an event object
• Now, knowing that you have been redirected to E7 from the last selected cell in "Painel de Embarques" - you can retrieve data from the respective row and paste it into "Atualizar Embarque"

Sample script:

function onSelectionChange(e){
  if(e.range.getSheet().getName() == "Painel de Embarques" && e.range.getValue()== "Editar"){
    PropertiesService.getScriptProperties().setProperty("row", e.range.getRow());
  }else if(e.range.getSheet().getName() == "Atualizar Embarque" && e.range.getA1Notation() == "E7"){
    var row = Number(PropertiesService.getScriptProperties().getProperty("row"));
    if(row){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var dataSheet = ss.getSheetByName("Painel de Embarques");
      var valueB = dataSheet.getRange(row, 2).getValue();
      var valueF =dataSheet.getRange(row, 6).getValue();
      var destSheet = ss.getSheetByName("Atualizar Embarque");
      destSheet.getRange("B19").setValue(valueB);
      destSheet.getRange("E7").setValue(valueF);
      Logger.log(e.range.getA1Notation());
      Logger.log(e.range.getSheet().getName());
      Logger.log(e.range.getValue());
    }
  }
}

• Mind that this approach can lead to undesired behavior, e.g. if you manually click into E7 without being redirected by clicking on the hyperlink.
• If this is not a satisfactory solution for you - you should think about another approach, e.g. creating a button and assign a script to it
• In this case you can implement the redirection and data copying on button click instead of on hyperlink click.