Is there an easy way to get the underlying URL for the cells in a google sheet using the google Sheets API?

0
votes

I am using the Google Sheets and Slides APIs to read data from a sheet and create Google slides.

Some cells contain links, and I want to be able to show them as links in the slides as well. Is there a way to get the hyperlink for the cell using the Google Sheets API?

I am getting the value in the cell by the following :

var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl); 
var sheet = ss.getSheetByName('countryData'); 
// Use the Sheets API to load data, one record per row. 
var values = sheet.getRange('A2:W10').getValues(); 
for (var i = 0; i < values.length; ++i) { 
    var row = values[i]; var country = row[0];
    //name in column 1 
    Logger.log(country);
}

Is there a method on the values that I can call to get the hyperlink on that particular cell? (e.g. for the value Afghanistan, the hyperlink on sheet is the BBC page for Afg.)

I am using the following to replace the token country in the slides using : shape.getText().replaceAllText('{{country}}',country);

1
google-apps-scripthyperlinkgoogle-sheets-apigoogle-slides-api
Can you share what you have tried so far? Did you try inserting them as strings? – Aerials
I am getting the value in the cell by the following : var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl); var sheet = ss.getSheetByName('countryData'); // Use the Sheets API to load data, one record per row. var values = sheet.getRange('A2:W10').getValues(); for (var i = 0; i < values.length; ++i) { var row = values[i]; var country = row[0];//name in column 1 Logger.log(country); Is there a method on the values that I can call to get the hyperlink on that particular cell? – Anupma
e.g. for the value Afghanistan, the hyperlink on sheet is the BBC page for Afg. I am using the following to replace the token country in the slides using : shape.getText().replaceAllText('{{country}}',country); – Anupma
Please edit your question and what you have tried. As it will be easier to read all together. Also, it appears you are using Apps script and not the Sheets API. And if you have a sample sheet, post the link to it too. – Aerials
Updated my answer with a solution. – Aerials

1 Answers

1
votes

From your additional information I see you are using Apps script.

To get the url of the links of the countries, you can use the Range.getFormula() method. However, you need to use the formula: =HYPERLINK(url, [linkLabel]) in your sheet. Then you can with the following code for example, get the url with Apps script:

function getHyperlinkUrl(range) {
  // The range should use the sheets formula: `=HYPERLINK(url,[linkLabel])`
  // Example: HYPERLINK("http://www.venezuela.com/", "Venezuela")
  
  var url = range.getFormula().split('"')[1];
  Logger.log(url);
  // Output: http://www.venezuela.com/
}

With that value you can build a textRun object like the following:

"textRun": {
    "content": "<YOUR-HYPERLINK-FROM-SHEETS>",
    "style": {
        "foregroundColor": {
            "opaqueColor": {
                "themeColor": "HYPERLINK"
            }
        },
        "link": {
            "url": "<YOUR-HYPERLINK-FROM-SHEETS>"
        },
        "underline": true
    }

and insert it into a slide with by using the presentations.batchUpdate method.

References: