Google App Script to create link in Google Slides

Question

I'm pulling data from a Google Sheet to create new Google Slides from a template. Everything works fine, but I can't figure out how to make text clickable as a link once I've replaced the template variable with the spreadsheet data.

How do I replace my website variable, company_website, with the website link AND make it clickable?

function createSlidesfromSheets() {
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/xxxxxlink/edit"; //make sure this includes the '/edit at the end
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  var deck = SlidesApp.getActivePresentation();
  var sheet = ss.getSheetByName('Sheet1'); // this needs to be the name of the sheet/feuille
  var values = sheet.getRange('A2:M20').getValues(); //this is the range of data to create the slides from. 
  //Logger.log(values);
  var slides = deck.getSlides();
  var templateSlideOne = slides[0];
  var templateSlideTwo = slides[1];
  var presLength = slides.length;
  
  values.forEach(function(page){
  if(page[0]){
    
   var company_name = page[0];
   var company_country = page[1];   
   var company_city = page[2];   
   var company_website = page[3];   
   var company_description = page[4];
   

    
   templateSlideOne.duplicate(); //duplicate the first template slide
   templateSlideTwo.duplicate(); //duplicate the second template slide
   slides = deck.getSlides(); //update the slides array for indexes and length
   
   newSlideOne = slides[1]; // declare the copy to update of the first template slide (right after the first template's slide)
   newSlideTwo = slides[3]; // declare the copy to update of the second template slide (right after the second template's slide)
    
   var firstShapes = (newSlideOne.getShapes());
     firstShapes.forEach(function(shape){
       shape.getText().replaceAllText('{{company-name}}',company_name);
       shape.getText().replaceAllText('{{company-city}}',company_city);       
       shape.getText().replaceAllText('{{company-country}}',company_country);
       shape.getText().replaceAllText('{{company-website}}',company_website);

// how do I use setLinkUrl() to make the new company_website text a clickable link?
       
    }); 
   var secondShapes = (newSlideTwo.getShapes());
    secondShapes.forEach(function(shape){
       shape.getText().replaceAllText('{{company-name}}',company_name);
       shape.getText().replaceAllText('{{company-description}}',company_description);       

    }); 
   presLength = slides.length; 
   newSlideOne.move(presLength);
   presLength = slides.length; 
   newSlideTwo.move(presLength); 
  } // end our conditional statement
  }); //close our loop of values

}

Tanaike · Accepted Answer · 2020-08-24 07:05:50Z

3

I believe your goal as follows.

You want to give the hyperlink to shape.getText().replaceAllText('{{company-website}}',company_website) in your script.

In order to achieve this, how about this modification?

From:

     firstShapes.forEach(function(shape){
       shape.getText().replaceAllText('{{company-name}}',company_name);
       shape.getText().replaceAllText('{{company-city}}',company_city);       
       shape.getText().replaceAllText('{{company-country}}',company_country);
       shape.getText().replaceAllText('{{company-website}}',company_website);

// how do I use setLinkUrl() to make the new company_website text a clickable link?
       
    });

To:

firstShapes.forEach(function(shape){
  var text = shape.getText();
  text.replaceAllText('{{company-name}}',company_name);
  text.replaceAllText('{{company-city}}',company_city);       
  text.replaceAllText('{{company-country}}',company_country);
  var n = text.replaceAllText('{{company-website}}',company_website);
  if (n > 0) text.getTextStyle().setLinkUrl(company_website);
});

In this modification, TextStyle is retrieved from TextRange, and setLinkUrl is used. At that time, the returned value from replaceAllText is used as the check of replace.

References:

Added: Answer for 2nd question

And about your new question of Does setLinkUrl(startOffset, endOffsetInclusive, url) not work in this context?, I think that there is no method of setLinkUrl(startOffset, endOffsetInclusive, url) in Slides service. I thought that this might be the method of Document service. Ref So this cannot be directly used. If you want to reflect the link to the part of text, please use

Sample script:

firstShapes.forEach(function(shape){
  var text = shape.getText();
  text.replaceAllText('{{company-name}}',company_name);
  text.replaceAllText('{{company-city}}',company_city);       
  text.replaceAllText('{{company-country}}',company_country);
  var n = text.replaceAllText('{{company-website}}',company_website);
  if (n > 0) text.getRange(startOffset, endOffset).getTextStyle().setLinkUrl(company_website);  // Modified
});

In this case, please set startOffset, endOffset.

Reference:

getRange(startOffset, endOffset)

edited Aug 24, 2020 at 7:05

answered Aug 23, 2020 at 21:45

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yobobos Over a year ago

excellent, thank you, Tanaike. Does setLinkUrl(startOffset, endOffsetInclusive, url) not work in this context?

Tanaike Over a year ago

@yobobos Thank you for replying. At first, can I ask you whether my answer could resolve your issue? If that didn't resolve your issue, I have to apologize. And about your new question, I added one more sample script. Could you please confirm it?

Collectives™ on Stack Overflow

Google App Script to create link in Google Slides

1 Answer 1

From:

To:

References:

Added: Answer for 2nd question

Sample script:

Reference:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

References:

Added: Answer for 2nd question

Sample script:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related