How to copy-paste a cell with hyperlinked text in Google Sheet?

user_13702898 0
Viewed 1241

I have a range in spreadsheet A, I want to copy it to spreadsheet B. There is a column in the original range that contains hyperlinked text.

When I tried to assign the original range from spreadsheet A to an array (getRange.getValues()) and then assign these value to another range in spreadsheet B (getRange.setValues()) I got the values, but it was just text whereas I expected hyperlinked text.

Please, help me get hyperlinked text from the original range.

google-apps-script google-sheets
1 Answers

I believe your goal as follows.

  • You want to copy the cells with the hyperlinks from the source sheet in the source Google Spreadsheet to the destination sheet in the destination Google Spreadsheet using Google Apps Script.

For this, how about this answer? In this case, I think that your goal can be achieved by copying the RichTextValues from the source to the destination. In this case, getRichTextValues and setRichTextValues are used instead of getValues and setValues, respectively.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function myFunction() {
  // Please set the source Spreadsheet ID, sheet name and range.
  const sourceSpreadsheetId = "###";
  const sourceSheetName = "Sheet1";
  const sourceRange = "A1:B10";

  // Please set the destination Spreadsheet ID, sheet name and range.
  const destinationSpreadsheetId = "###";
  const destinationSheetName = "Sheet1";
  const destinationRange = "A1:B10";

  const src = SpreadsheetApp.openById(sourceSpreadsheetId).getSheetByName(sourceSheetName).getRange(sourceRange);
  const dst = SpreadsheetApp.openById(destinationSpreadsheetId).getSheetByName(destinationSheetName).getRange(destinationRange);

  // Modified.
  const richTextValues = src.getRichTextValues();
  src.getValues().forEach((r, i) => {
    r.forEach((c, j) => {
      if (typeof c == "number") {
        richTextValues[i][j] = SpreadsheetApp.newRichTextValue().setText(c).build();
      }
    });
  });
  dst.setRichTextValues(richTextValues);
}

Note:

  • This is a simple sample script. So please modify this for your actual situation.
  • I could confirm your issue. I could confirm that when the values are retrieved by getRichTextValues, the number values are not included. I reported this to the issue tracker.

References:

user_71086530
Related