Preserve a hyperlink URL when using JS to copy a link in a cell to another sheet

Question

In my spreadsheet, the cell at reference point (28, 9) contains a hyperlink. The code then copies the contents of that cell to another sheet in the same spreadsheet. Right now it will copy the text, but the new cell only contains the text and not the hyperlink properties. Is there a way to preserve the hyperlink through the transfer? I tried looking up hyperlink information, but could not find an answer...or at least not one I understood. I have only been coding for about 2 weeks, so still not understanding a lot. Here is my code:

function submitButtonClick() {   
  
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  Logger.log('sheet.getName(): ' + sheet.getName());
  
  
  if (sheet.getName() !== "SubmitReceipt") {return;};
  
  var targetSheet = ss.getSheetByName("ReceiptRecord");
  
  var arrayOfData = [];
   
  var week = sheet.getRange(6,9).getValue();
  var emplN = sheet.getRange(4,9).getValue();
  var purDate = sheet.getRange(9,9).getValue();
  var purFrom = sheet.getRange(11,9).getValue();
  var custC = sheet.getRange(14,9).getValue();
  var deptC = sheet.getRange(16,9).getValue();
  var lotC = sheet.getRange(18,9).getValue();
  var laborC = sheet.getRange(20,9).getValue();
  var itemC = sheet.getRange(22,9).getValue();
  var hyperL = sheet.getRange(28,9).getValue();
  var notes = sheet.getRange(44,8).getValue();
  
  arrayOfData[0] = week;
  arrayOfData[1] = emplN;
  arrayOfData[2] = purDate;
  arrayOfData[3] = purFrom;
  arrayOfData[4] = custC;
  arrayOfData[5] = deptC;
  arrayOfData[6] = lotC;
  arrayOfData[7] = laborC;
  arrayOfData[8] = itemC;
  arrayOfData[9] = notes;
  arrayOfData[10] = hyperL;
  
  Logger.log('arrayOfData '+ arrayOfData)
  
  var lastRow = targetSheet.getLastRow();
  
  Logger.log('lastRow: ' + lastRow);
  Logger.log('arraylength ' + arrayOfData.length);
  
  targetSheet.getRange(lastRow+1, 1, 1, arrayOfData.length).setValues([arrayOfData]);


  sheet.getRange(9,9).clearContent();
  sheet.getRange(11,9).clearContent();
  sheet.getRange(14,9).clearContent();
  sheet.getRange(16,9).clearContent();
  sheet.getRange(18,9).clearContent();
  sheet.getRange(20,9).clearContent();
  sheet.getRange(22,9).clearContent();
  sheet.getRange(28,9).clearContent();
  sheet.getRange(44,8).clearContent();
  
}

Thank you in advance for your help.

UPDATE: I did find a possible solution and tried it, but then got an error "TypeError: Cannot find function getFormulaR1C1 in object Sample Receipt 6.jpg. (line 26, file "Submit to Record")."

Here is the updated line 26 to show the new solution I tried:

  var url = hyperL.getFormulaR1C1();

Thanks again for helping.

JLBJones JLBJones · Accepted Answer · 2016-02-13T00:40:36

I figured it out. Notice the differences between the "//Changes" comments.

function submitButtonClick() {   
  
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  Logger.log('sheet.getName(): ' + sheet.getName());
  
  
  if (sheet.getName() !== "SubmitReceipt") {return;};
  
  var targetSheet = ss.getSheetByName("ReceiptRecord");
  
  var arrayOfData = [];
   
  var week = sheet.getRange(6,9).getValue();
  var emplN = sheet.getRange(4,9).getValue();
  var purDate = sheet.getRange(9,9).getValue();
  var purFrom = sheet.getRange(11,9).getValue();
  var custC = sheet.getRange(14,9).getValue();
  var deptC = sheet.getRange(16,9).getValue();
  var lotC = sheet.getRange(18,9).getValue();
  var laborC = sheet.getRange(20,9).getValue();
  var itemC = sheet.getRange(22,9).getValue();
  var hyperL = sheet.getRange(28,9).getValue();
  var notes = sheet.getRange(44,8).getValue();
  
  
  
  
  //Changes
  
  var range = SpreadsheetApp.getActiveSheet().getRange(28,9,1,3);
  
  Logger.log('range= ' + range.getValue());
  
  Logger.log('hyperlink: ' + range.getFormulaR1C1());
  
  var url = /"(.*?)"/.exec(range.getFormulaR1C1())[1];
  
  Logger.log('url: ' + url);
  
  //Changes
  
  
  
  arrayOfData[0] = week;
  arrayOfData[1] = emplN;
  arrayOfData[2] = purDate;
  arrayOfData[3] = purFrom;
  arrayOfData[4] = custC;
  arrayOfData[5] = deptC;
  arrayOfData[6] = lotC;
  arrayOfData[7] = laborC;
  arrayOfData[8] = itemC;
  arrayOfData[9] = notes;
  arrayOfData[10] = hyperL;
  arrayOfData[11] = url;
  
  Logger.log('arrayOfData '+ arrayOfData)
  
  var lastRow = targetSheet.getLastRow();
  
  Logger.log('lastRow: ' + lastRow);
  Logger.log('arraylength ' + arrayOfData.length);
  
  targetSheet.getRange(lastRow+1, 1, 1, arrayOfData.length).setValues([arrayOfData]);


  sheet.getRange(9,9).clearContent();
  sheet.getRange(11,9).clearContent();
  sheet.getRange(14,9).clearContent();
  sheet.getRange(16,9).clearContent();
  sheet.getRange(18,9).clearContent();
  sheet.getRange(20,9).clearContent();
  sheet.getRange(22,9).clearContent();
  sheet.getRange(28,9).clearContent();
  sheet.getRange(44,8).clearContent();
  
}

Preserve a hyperlink URL when using JS to copy a link in a cell to another sheet

1 Answers