Add hyperlink efficiently to text just replaced on Google Slides

Question

I am inserting a list of instagram handles from Google Sheets to Google Slides using Google Apps Script.

• The Google Sheets contains a list of instagram handles, such as @Apple, @Google, etc. For convenience, in the recreation below, let's assume that the handle "@Apple" has already been retrieved from the sheet as a string and placed into a variable called handle.
• The Google slide contains a single placeholder in a textbox, {{handle}}.

My goal is to insert "@Apple" into the placeholder {{handle}}, and add a hyperlink (https://www.instagram.com/apple/) to it. I.e., I want to add a hyperlink to the text I just inserted/replaced.

I am able to achieve this with the code below, but it is extremely inefficient. In my actual application, there are a lot of entries to insert, and many more shapes on the slides, so I would love to be able to do this efficiently. I am open to trying other methods too. Any guidance is appreciated.

My code:

var handle = "@Apple";

// extract username to get url
let regExp = new RegExp("[^@]*$");
var username = regExp.exec(handle)[0];
var url = "https://www.instagram.com/" + username;

// insert the handle at {{handle}} and make it a hyperlink
var allShapes = (currentSlide.getShapes());
allShapes.forEach(function(shape) {
    var text = shape.getText();
    var n = text.replaceAllText("{{handle}}", handle);
    if (n > 0) {
        var textRange = text.getTextStyle();
        text.find(handle).forEach((v) => {
            v.getTextStyle().setLinkUrl(url);
        });
    }
});

Answer 1

I believe your goal is as follows.

• You want to reduce the process cost of your script.

In your situation, how about using Slides API? When Slides API is used, your script can be modified as follows. I guessed that when Slides API is used, the process cost can be reduced.

In this modification, from The Google Sheets contains a list of instagram handles, such as @Apple, @Google, etc., it supposes that several replace texts are used. The modified script is as follows.

Modified script:

Please copy and paste the following script to the script editor of Google Slide. And, please enable Slides API at Advanced Google services.

First, please set handles for your actual situation. In this modification, you can use multiple replacement texts. In this sample, the shapes with the text {{handle}} are replaced with @Apple. The shapes with the text {{handle2}} are replaced with @Google. And, the hyperlinks are set.

function myFunction() {
  // Prepare values.
  var handles = [ // Please prepare this object for your actual situation.
    { replaceFrom: "{{handle}}", replaceTo: "@Apple" },
    { replaceFrom: "{{handle2}}", replaceTo: "@Google" },
  ];
  handles.forEach(e => {
    let regExp = new RegExp("[^@]*$");
    var username = regExp.exec(e.replaceTo)[0];
    var url = "https://www.instagram.com/" + username;
    e.url = url;
  });

  // Replace texts and set hyperlinks.
  var s = SlidesApp.getActivePresentation();
  var sId = s.getId();
  var currentSlide = s.getSelection().getCurrentPage();
  var pageId = currentSlide.getObjectId();
  var obj = Slides.Presentations.Pages.get(sId, pageId);
  var requests = handles.map(({ replaceFrom, replaceTo, url }) => {
    var temp = [{ replaceAllText: { containsText: { text: replaceFrom }, replaceText: replaceTo, pageObjectIds: [pageId] } }];
    obj.pageElements.forEach(e => e.shape.text.textElements.forEach(f => {
      var t = f?.textRun?.content;
      if (t && t.trim() == replaceFrom) {
        temp.push({ updateTextStyle: { style: { link: { url } }, objectId: e.objectId, fields: "link" } });
      }
    }));
    return temp;
  });
  if (requests.length == 0) return;
  Slides.Presentations.batchUpdate({ requests }, sId);
}

• When this script is run, the selected page of Google Slide is processed with handles by one API call.

Note:

• As an important point, from your showing script, the URL is created using your script. Please be careful about this.
• From your script, only the shapes are searched. Please be careful about this.

References:

• Method: presentations.get
• Method: presentations.batchUpdate

Added:

From your following reply,

but my situation is as follows: For the list of handles on my Google sheet (in a single column), for each row, I will 1. create a new slide from a template, with {{handle}} written inside, 2. replace {{handle}} the handle (e.g. @Apple), 3. insert the url. So instead of updating multiple locations on a single slide, I'm actually creating a slide every time I read a new row. Is it still possible to use batchUpdate in this situation?

Unfortunately, from your reply, I couldn't understand the column number from in a single column. So, in this sample script, it supposes that it's column "A" with the header row. And also, unfortunately, I couldn't understand create a new slide from a template. So, in this sample script, it is supposed that the 1st-page slide in a Google Slide is a template slide. And, the 1st template slide is copied. When these are reflected in a sample script, it becomes as follows.

Sample script:

In this sample script, please copy and paste the script to the script editor of Google Spreadsheet. And, please set templateSlideId and sheetName. It supposes that Google Slide of templateSlideId has a template slide on the 1st page. Please be careful about this.

This script uses Slides API. Please be careful about this.

function myFunction() {
  // Please set your template Google Slide ID.
  // In this script, the 1st slide is used by copying.
  var templateSlideId = "###";
  var sheetName = "Sheet1"; // Please set your sheet name.

  // Prepare values.
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var values = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().filter(([a]) => a);
  var handles = values.map(([a]) => ({ replaceFrom: "{{handle}}", replaceTo: a }))
  handles.forEach(e => {
    let regExp = new RegExp("[^@]*$");
    var username = regExp.exec(e.replaceTo)[0];
    var url = "https://www.instagram.com/" + username;
    e.url = url;
  });

  // Duplicate a template slide and replace texts and set hyperlinks.
  var topPageId = SlidesApp.openById(templateSlideId).getSlides()[0].getObjectId();
  var requests1 = [...Array(handles.length)].map((_, i) => ({ duplicateObject: { objectId: topPageId } }));
  Slides.Presentations.batchUpdate({ requests: requests1 }, templateSlideId);
  var [, ...obj] = Slides.Presentations.get(templateSlideId).slides;
  var requests = handles.map(({ replaceFrom, replaceTo, url }, i) => {
    var temp = [{ replaceAllText: { containsText: { text: replaceFrom }, replaceText: replaceTo, pageObjectIds: [obj[i].objectId] } }];
    obj[i].pageElements.forEach(e => e.shape.text.textElements.forEach(f => {
      var t = f?.textRun?.content;
      if (t && t.trim() == replaceFrom) {
        temp.push({ updateTextStyle: { style: { link: { url } }, objectId: e.objectId, fields: "link" } });
      }
    }));
    return temp;
  });
  if (requests.length == 0) return;
  Slides.Presentations.batchUpdate({ requests }, templateSlideId);
}

When this script is run, the 1st page (template slide) of the Google Slide is copied. And, the text {{handle}} is replaced using each row of cells "A2:A" and set the hyperlinks.

Answer 2

ALTERNATIVE SOLUTION

You may also use this code to achieve what you'd like to do:

const myFunction = () => {
  const handles = ["@Apple", "@Google", "@Microsoft"];
  const slides = SlidesApp.getActivePresentation().getSlides();

  slides.forEach((slide, i) => {
    const handle = handles[i];
    const shapes = slide.getShapes();

    shapes.forEach((shape) => {
      const text = shape.getText();
      const n = text.replaceAllText("{{handle}}", handle);

      if (n > 0) {
        const regExp = new RegExp("[^@]*$");
        const username = regExp.exec(handle)[0];
        const url = "https://www.instagram.com/" + username;
        text.find(handle).forEach((v) => {
          v.getTextStyle().setLinkUrl(url);
        });
      }
    });
  });
}

You're already getting what you'd like, this was just made to loop through all the slides and replace all of {{handle}} with what you'll put in the array handles.

OUTPUT

REFERENCE

• Class Slide

UPDATE

Since you'll be using a spreadsheet, you may change this:

const handles = ["@Apple", "@Google", "@Microsoft"];

To:

const ss = SpreadsheetApp.openById("abc1234567").getSheetByName("Sheet1");
const handles = ss.getRange(2, 1, ss.getLastRow() - 1).getValues();

Note: Like with @Tanaike's updated answer, I'm also assuming that your Instagram handles are on A2:A.

The full code would become:

const myFunction = () => {
  const ss = SpreadsheetApp.openById("abc1234567").getSheetByName("Sheet1");
  const handles = ss.getRange(2, 1, ss.getLastRow() - 1).getValues();
  const slides = SlidesApp.getActivePresentation().getSlides();

  slides.forEach((slide, i) => {
    const handle = handles[i];
    const shapes = slide.getShapes();

    shapes.forEach((shape) => {
      const text = shape.getText();
      const n = text.replaceAllText("{{handle}}", handle);

      if (n > 0) {
        const regExp = new RegExp("[^@]*$");
        const username = regExp.exec(handle)[0];
        const url = "https://www.instagram.com/" + username;
        text.find(handle).forEach((v) => {
          v.getTextStyle().setLinkUrl(url);
        });
      }
    });
  });
}

OUTPUT