google drive file link automatically change format in spreadsheet-CodePudding

I am implementing some download and upload file functionality in google drive through google app-script storing the drive link in google sheet. upload works fine but after some time the link is turn into some kind of hyperlink as like below

so that's why I am no longer able to get the link simply writting .getDisplayValue() const ss = SpreadSheetApp.getActive().getActiveSheet() let url = ss.getRange().getDisplayValue()

Any Suggestion ...?

I tried Adding .getRichTextValue().getLinkUrl() But It also does not worked as It is not a HyperLink

CodePudding user response：

Issue and workaround:

From your sample image, in your situation, the link of the file is changed to the smart chip. In the current stage, unfortunately, there are no methods for managing the smart chips on a Spreadsheet. So, in this case, it is required to use a workaround. The workaround is as follows.

Convert Google Spreadsheet to XLSX data.
- By this, the file links of the smart chip are converted to simple strings and hyperlinks.
Convert XLSX data to Google Spreadsheet.
Retrieve the hyperlinks from the cells.

This method is from How to get in Apps Script the value of a dropdown in a Google Doc? and https://tanaikech.github.io/2022/10/27/retrieving-values-of-calendar-events-of-smart-chips-on-google-document-using-google-apps-script/ .

When this flow is reflected in a sample script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Google Spreadsheet and set range that you want to retrieve the hyperlinks as A1Notation. In this sample, Drive API is used. So, please enable Drive API at Advanced Google services.

function myFunction() {
  const range = "Sheet1!A1:A10"; // Please set the range you want to retrieve the hyperlinks.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id="   ss.getId();
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } }).getBlob();
  const tempId = Drive.Files.insert({ mimeType: MimeType.GOOGLE_SHEETS, title: "temp" }, blob).id;
  const tempFile = DriveApp.getFileById(tempId);
  const tempSS = SpreadsheetApp.open(tempFile);
  const res = tempSS.getRange(range).getRichTextValues().map((r, i) => r.map((c, j) => ({ value: c.getText(), url: c.getLinkUrl() || "", range: { row: i   1, column: j   1 } })));
  tempFile.setTrashed(true);
  console.log(res);
}

Testing:

When this script is run, the following result is obtained.

[
  [{"value":"sample value","url":"https://drive.google.com/file/d/###/view?usp=share_link","range":{"row":1,"column":1}}],
  ,
  ,
  ,
]

Note:

As another approach, in your showing sample image, if you want to convert the file links of the smart chip to the normal value with the hyperlink, how about the following sample script? In this sample, range is overwritten by the normal values with the hyperlinks obtained by converting from XLSX data.

function myFunction2() {
  const range = "Sheet1!A1:A10"; // Please set the range you want to retrieve the hyperlinks.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const url = "https://docs.google.com/spreadsheets/export?exportFormat=xlsx&id="   ss.getId();
  const blob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer "   ScriptApp.getOAuthToken() } }).getBlob();
  const tempId = Drive.Files.insert({ mimeType: MimeType.GOOGLE_SHEETS, title: "temp" }, blob).id;
  const tempFile = DriveApp.getFileById(tempId);
  const tempSS = SpreadsheetApp.open(tempFile);
  const r = tempSS.getRange(range);
  const tempSheet = r.getSheet().copyTo(ss);
  tempSheet.getRange(r.getA1Notation()).copyTo(ss.getRange(range));
  ss.deleteSheet(tempSheet);
  tempFile.setTrashed(true);
}

Issue and workaround:

Sample script:

Testing:

Note:

References: