how can I return Hyperlink from google apps script

Question

//Link_generator 

function lk(num01) {
  try{
     var sheets = SpreadsheetApp.getActiveSpreadsheet()
     var out_01 = new Array();
  
               if(num01==1){var sheetnames = ["01.01","01.02","01.03","01.04","01.05","01.06","01.07","01.08","01.09","01.10","01.11","01.12","01.13","01.14","01.15","01.16","01.17","01.18","01.19","01.20","01.21","01.22","01.23","01.24","01.25","01.26","01.27","01.28","01.29","01.30","01.31"];}
               if(num01==2){var sheetnames = ["02.01","02.02","02.03","02.04","02.05","02.06","02.07","02.08","02.09","02.10","02.11","02.12","02.13","02.14","02.15","02.16","02.17","02.18","02.19","02.20","02.21","02.22","02.23","02.24","02.25","02.26","02.27","02.28","02.29"];}
               if(num01==3){var sheetnames = ["03.01","03.02","03.03","03.04","03.05","03.06","03.07","03.08","03.09","03.10","03.11","03.12","03.13","03.14","03.15","03.16","03.17","03.18","03.19","03.20","03.21","03.22","03.23","03.24","03.25","03.26","03.27","03.28","03.29","03.30","03.31"];}
               if(num01==4){var sheetnames = ["04.01","04.02","04.03","04.04","04.05","04.06","04.07","04.08","04.09","04.10","04.11","04.12","04.13","04.14","04.15","04.16","04.17","04.18","04.19","04.20","04.21","04.22","04.23","04.24","04.25","04.26","04.27","04.28","04.29","04.30"];}
               if(num01==5){var sheetnames = ["05.01","05.02","05.03","05.04","05.05","05.06","05.07","05.08","05.09","05.10","05.11","05.12","05.13","05.14","05.15","05.16","05.17","05.18","05.19","05.20","05.21","05.22","05.23","05.24","05.25","05.26","05.27","05.28","05.29","05.30","05.31"];}
               if(num01==6){var sheetnames = ["06.01","06.02","06.03","06.04","06.05","06.06","06.07","06.08","06.09","06.10","06.11","06.12","06.13","06.14","06.15","06.16","06.17","06.18","06.19","06.20","06.21","06.22","06.23","06.24","06.25","06.26","06.27","06.28","06.29","06.30"];}
               if(num01==7){var sheetnames = ["07.01","07.02","07.03","07.04","07.05","07.06","07.07","07.08","07.09","07.10","07.11","07.12","07.13","07.14","07.15","07.16","07.17","07.18","07.19","07.20","07.21","07.22","07.23","07.24","07.25","07.26","07.27","07.28","07.29","07.30","07.31"];}
               if(num01==8){var sheetnames = ["08.01","08.02","08.03","08.04","08.05","08.06","08.07","08.08","08.09","08.10","08.11","08.12","08.13","08.14","08.15","08.16","08.17","08.18","08.19","08.20","08.21","08.22","08.23","08.24","08.25","08.26","08.27","08.28","08.29","08.30","08.31"];}
               if(num01==9){var sheetnames = ["09.01","09.02","09.03","09.04","09.05","09.06","09.07","09.08","09.09","09.10","09.11","09.12","09.13","09.14","09.15","09.16","09.17","09.18","09.19","09.20","09.21","09.22","09.23","09.24","09.25","09.26","09.27","09.28","09.29","09.30"];}
               if(num01==10){var sheetnames = ["10.01","10.02","10.03","10.04","10.05","10.06","10.07","10.08","10.09","10.10","10.11","10.12","10.13","10.14","10.15","10.16","10.17","10.18","10.19","10.20","10.21","10.22","10.23","10.24","10.25","10.26","10.27","10.28","10.29","10.30","10.31"];}
               if(num01==11){var sheetnames = ["11.01","11.02","11.03","11.04","11.05","11.06","11.07","11.08","11.09","11.10","11.11","11.12","11.13","11.14","11.15","11.16","11.17","11.18","11.19","11.20","11.21","11.22","11.23","11.24","11.25","11.26","11.27","11.28","11.29","11.30"];}
               if(num01==12){var sheetnames = ["12.01","12.02","12.03","12.04","12.05","12.06","12.07","12.08","12.09","12.10","12.11","12.12","12.13","12.14","12.15","12.16","12.17","12.18","12.19","12.20","12.21","12.22","12.23","12.24","12.25","12.26","12.27","12.28","12.29","12.30","12.31"];}
    
  for (var i = 0 ; i < sheetnames.length ; i++ ){
     var k=i+1;
    out_01.push('=hyperlink("#gid='+sheets.getSheetByName(sheetnames[i]).getSheetId()+'",'+k+')');     
  }
var division = chunk (out_01 , 7) ;
  
    return  division 

}
  catch (err){
    return "#ERROR!"
    }
}



function chunk(arr, size) {
    var i, j, temparray = [], chunk = size;
    for (i = 0, j = arr.length; i < j; i += chunk) {
        temparray.push(arr.slice(i, i + chunk));
    }
    return temparray
}

I want to make hyperlink in google spreadsheet by apps script

but this cell custom function shows just 'hyperlink formula text'

I want real hyperlink

How can I fix this code?

here is my situation

Tanaike · Accepted Answer · 2022-03-18T15:07:27.250

As the other workaround, in your situation, how about converting the formulas as the string value to valid formulas? In this case, your script is not modified. The following function is added.

Usage:

1. Sample script:

Please add this function to your script editor including your functions and save the script. In this case, your showing script can be used without modifying.

function installOnEdit(e) {
  const range = e.range;
  const formula = range.getFormula();
  if (!(/^\=lk\(\d+\)$/i).test(formula)) return;
  const sheet = range.getSheet();
  const id = e.source.getId();
  const sheetName = sheet.getSheetName();
  const ranges = sheet.createTextFinder("^\\=hyperlink\\(.+\\)$").useRegularExpression(true).matchEntireCell(true).findAll().map(r => `'${sheetName}'!${r.getA1Notation()}`);
  const data = Sheets.Spreadsheets.Values.batchGet(id, { ranges }).valueRanges.map(({ range, values }) => ({ range, values }));
  Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, id);
}

2. Enable Sheets API.

This script uses Sheets API. Please enable Sheets API at Advanced Google services. The formulas as the string value are converted to the valid formulas using Sheets API.

3. Install OnEdit trigger.

This script is run by the installable OnEdit trigger, when the custom function of =lk(##) is put to a cell. Please install OnEdit trigger to the function of installOnEdit.

4. Testing.

When this script is used, for example, when you put =lk(1) to a cell, the following result is obtained.

References:

Added:

I noticed a bit simpler workaround than the above one. So I would like to add it.

Sample script:

In this case, both the installable OnEdit trigger and Sheets API are not required to be used. You can use this script by copying and pasting this script to your script editor and saving it.

When you use this script, for example, please put =lk(1) to a cell. By this, onEdit function is automatically run and the formulas are retrieved from your function of lk(). And then, the formulas are put to the cells. The result situation is the same as the above demonstration.

function onEdit(e) {
  const range = e.range;
  const formula = range.getFormula();
  if (!(/^\=lk\(\d+\)$/i).test(formula)) return;
  const arg = formula.match(/^\=lk\((\d+)\)$/i)[1];
  const formulas = lk(Number(arg));
  const max = Math.max(...formulas.map(r => r.length));
  const res = formulas.map(r => r.length < max ? [...r, ...Array(max - r.length).fill("")] : r);
  range.offset(0, 0, res.length, res[0].length).setFormulas(res);
}

Thank you. that's a really cool solution. It's amazing that you can detour like this — GM B, Mar 19 '22 at 04:01
@GM B Thank you for replying and testing it. I'm glad your question was resolved. Thank you, too. — Tanaike, Mar 19 '22 at 05:00

score 0 · Answer 2 · answered Mar 18 '22 at 11:04

Issue:

Custom functions can only return values (strings, numbers, etc.), not formulas. A "formula" returned by a custom function will just be interpreted as a string starting with =.

Related requests in Issue Tracker:

Workaround 1:

Modify your custom function so that it just returns the url part (i.e. #gid=...) and use that returned value in a =HYPERLINK() formula which is called conventionally by your sheet:

function lk(num01,sheetIndex) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var sheetnames;
  switch (num01) {
    case 1:
      sheetnames = ["01.01","01.02","01.03","01.04","01.05","01.06","01.07","01.08","01.09","01.10","01.11","01.12","01.13","01.14","01.15","01.16","01.17","01.18","01.19","01.20","01.21","01.22","01.23","01.24","01.25","01.26","01.27","01.28","01.29","01.30","01.31"];
      break;
    case 2:
      sheetnames = ["02.01","02.02","02.03","02.04","02.05","02.06","02.07","02.08","02.09","02.10","02.11","02.12","02.13","02.14","02.15","02.16","02.17","02.18","02.19","02.20","02.21","02.22","02.23","02.24","02.25","02.26","02.27","02.28","02.29"];
      break;
    // Other cases
  }
  return '#gid=' + sheets.getSheetByName(sheetnames[sheetIndex]).getSheetId();
}

And then, call it in each cell, for example like: =HYPERLINK(lk(1,0),1):

A considerable downside to this is that you'd have to call the custom function for each cell, which would slow down the process (ref: Optimization). Therefore, I would not recommend this approach.

Workaround 2:

Don't use custom functions for this, but call your function via other contexts, so that methods like setFormulas or setRichTextValues can be used (these methods cannot be used in the context of custom functions - ref: Using Apps Script services).

For example, you could do something like this (not via a custom function, but maybe via a custom menu - with a prompt dialog to provide the parameters, or what have you - or, alternatively, via onEdit trigger):

function lk(num01=1,TARGET_SHEET = "Sheet where hyperlinks will be written") {
  var sheets = SpreadsheetApp.getActiveSpreadsheet();
  var out_01 = new Array();
  var sheetnames;
  switch (num01) {
    case 1:
      sheetnames = ["01.01","01.02","01.03","01.04","01.05","01.06","01.07","01.08","01.09","01.10","01.11","01.12","01.13","01.14","01.15","01.16","01.17","01.18","01.19","01.20","01.21","01.22","01.23","01.24","01.25","01.26","01.27","01.28","01.29","01.30","01.31"];
      break;
    case 2:
      sheetnames = ["02.01","02.02","02.03","02.04","02.05","02.06","02.07","02.08","02.09","02.10","02.11","02.12","02.13","02.14","02.15","02.16","02.17","02.18","02.19","02.20","02.21","02.22","02.23","02.24","02.25","02.26","02.27","02.28","02.29"];
      break;
    // Other cases
  }
  //for (var i = 0 ; i < sheetnames.length; i++ ){
  for (var i = 0 ; i < 2; i++ ){
    var k=i+1;
    out_01.push('=hyperlink("#gid='+sheets.getSheetByName(sheetnames[i]).getSheetId()+'",'+k+')');     
  }
  var division = chunk (out_01 , 7) ;
  var targetSheet = sheets.getSheetByName(TARGET_SHEET);
  targetSheet.getRange(1,1,division.length,division[0].length).setFormulas(division);
}