GoogleScript delete my HYPERLINK (Google SpreadSheet)

Question

Situation:

I have the following script, that run every 1 hours. This script insert "Update Age", I mean how many days past from DATE1 to DATE2.

Script 1: Script Link

Problem:

Column A: This column have an hyperlink. (Tickets# Numbers)
Column F & G: Date 1 and Date 2
Column H: The result of the Script 1

In the coulmn A, I have other script that insert Hyperlink... See Script Link 2.
This Script 2, run OnEdit but the Script 1 run every 1 hour.

When the script 1 run, the formula or Hyperlink is deleted only appear the data in cell.

Question:

Does exist any way that when the script 1 run, not delete the hyperlink or apply the hyperlink to all rows starting for the second 2 rows, the hyperlink.

SCRIPT FOR INSERT LINK:

function InsertLink(e)
{
  var actSht = e.source.getActiveSheet();
  if (actSht.getName() == ['ISP1']){

  var activeCell = actSht.getActiveCell(); //Detec the ActiveCell

  //var activeCell = event.range;
  var activeCellValue = e.value;

  var column = activeCell.getColumn();
  var colNums  = [1]; //Columns, whose edit is considered
  if(colNums.indexOf(column) == -1) return; //If column other than considered then return

  var row = activeCell.getRow();
  if(row < 2)   return; //If header row then return

  var length = String(activeCellValue).length;

  if (!e.value)
  {
    activeCell.setValue()
  }
  else if(length > 2)
  {
    activeCell.setValue('=HYPERLINK' + '("https://www.example.com/id='+activeCellValue+'";"'+activeCellValue+'")');
  }
}
}

SCRIPT UPDATE:

// Create a timer trigger that will call "shellUpdateAge" every 30 minutes
// This function will run only for this particular sheets
function shellUpdateAge(){
  var sheets = ['ISP1'];
  for (var s in sheets){
toUpdateAge(sheets[s]);
  }
}

function toUpdateAge(sheetName){
  var ss = SpreadsheetApp.openById('1WmEwSLzqxOj7xkjokmor5B_HpMdabbEAGXiYeQwpIl8');
  var sh = ss.getSheetByName(sheetName);
  var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
  for(var n=0;n<data.length;++n){
  if(typeof(data[n][6])=='object'){
data[n][7]=dayToToday(data[n][6])
  }
}
   sh.getRange(1,1,data.length,data[0].length).setValues(data)
}

function dayToToday(x){
  var refcell = x;;// get value in column A to get the reference date
  var refTime = new Date(refcell);
  var ref = refTime.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var today = new Date();
  var TD = today.setHours(0,0,0,0)/(24*3600000);// set hours, minutes, seconds and milliseconds to 0 if necessary and get number of days
  var day = parseInt(TD-ref);// get the difference in days (integer value )
  return day ; // return result that will be in cell
}

DATA: A2: =HYPERLINK("https://www.example.com/id=12345";"12345")

Note: The Hyperlink appear for every row in the column A when I ingress the Ticket ID.

F: 12/08/2014 18:08:00 (This Value is the DATA2)
G: 13/08/2014 18:08:00 (This Value is the DATE2)
H: The update age, insert the result from DATE2-DATE1. In this example the script will return 1 day.

Now the problem is when the script Update Age run, the script update the column G and delete the formula in column A.

Are you able to post the exact code you are using for Script 1? I presume you are getting the values for the whole row, and then setting them down again, hence over-writing the HYPERLINK formula. — AdamL
Hi, I updated... I think maybe If I insert into the "Update Age" script a line where re-write the Hyperlink formula for the column A starting for row 2 only if the cell have data or length > 4 caracteres... Or modify the UpdateAge for avoid over-writing the Column A .... I don't know... Sorry for the English — LAD Service Desk

AdamL AdamL · Accepted Answer · 2014-08-13T23:27:03

I would just get and set the bare minimum, so you're not involving the HYPERLINK column:

function toUpdateAge(sheetName){
  var ss = SpreadsheetApp.openById('1WmEwSLzqxOj7xkjokmor5B_HpMdabbEAGXiYeQwpIl8');
  var sh = ss.getSheetByName(sheetName);
  var range = sh.getRange(1,7,sh.getLastRow(),2);
  var data = range.getValues();
  for(var n=0;n<data.length;++n){
  if(typeof(data[n][0])=='object'){
    data[n][1]=dayToToday(data[n][0]);
  }
}
   range.setValues(data);
}

As an aside, you wouldn't consider a spreadsheet array formula for the date difference and HYPERLINK columns?