Preserve hyperlink when pasting from sheets to docs

Question

I am creating a Google Doc from a Google Sheet with a script. One cell is a hyperlink formula.

=HYPERLINK('-automatization-!P2,'-automatization-!P3)

That does not contain the link directly but includes a reference to it.

When copying it with the following code, the Google Doc either contains only the label (with getValue()) or the hyperlink formula that you can see above (with getFormula())

function createdoc() {
  documentId = DriveApp.getFileById(templateId).makeCopy().getId()
  DriveApp.getFileById(documentId).setName('XXXX ' + date);
  var body = DocumentApp.openById(documentId).getBody();
  for (var i = 2; i<sheet.getLastRow();i++){
  companyname = sheet.getRange(i,1).getValue()
  description = sheet.getRange(i,2).getValue()
  crunchbase = sheet.getRange(i,3).getFormula()
  body.appendParagraph(companyname);
  body.appendParagraph(description);
  body.appendParagraph(crunchbase);
}}

Does anybody have a solution to preserve the hyperlink when copying and pasting from Sheets to Docs?

Cooper Cooper · Accepted Answer · 2021-02-15T17:18:08

how about const ss=SpreadsheetApp.getActive();const sh=ss.getSheetByName('-automatization-');const url=sh.getRange('P2').getValue(); Note: I hardly ever use formulas so you may have to correct this.

Preserve hyperlink when pasting from sheets to docs

1 Answers