Insert the link into the keyword in the sheet file-CodePudding

Please help me:

I have a sheet file with 2 columns Column A is the keyword, column B is the link to insert the keyword. Eg:

Column A    Column B
Key1        Link1
Key2        Link2
...         ...

How to automatically find keywords in the DOCS file and then insert Link? Here is my idea, but it doesn't work

function insertLink() {
  var file,files,folder,folders,newestFileID;
  var filethaythe = DriveApp.getFilesByName('Set Link');
  var ss = SpreadsheetApp.open(filethaythe.next());//ID sheet thư viện thay thế
  SpreadsheetApp.setActiveSpreadsheet(ss);
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Trang tính1');
  var sheet = ss.getSheetByName('Trang tính1');
  var values = sheet.getDataRange().getValues();

  folders = DriveApp.getFoldersByName('test3');  
  while (folders.hasNext()) {
    folder = folders.next();
    files = folder.getFilesByType("application/vnd.google-apps.document");
    while (files.hasNext()){
      file = files.next();  
  var newestFileID = file.getId();
  Utilities.sleep(500);
  var currentDoc = DocumentApp.openById(newestFileID);
  var dongcuoi= sh.getLastRow();
  var dc = dongcuoi  1;
  var rgtxt = currentDoc.getBody();
  var rgrep = ss.getSheets()[0].getRange("A1:B" dc);
  var repA = rgrep.getValues().filter(r => r.every(c => c.toString()));
  repA.forEach(e => rgtxt.setLinkUrl(...e));
  currentDoc.saveAndClose();

    break
      

    }
  
};
}

CodePudding user response：

I believe your goal as follows.

You want to set the hyperlink to the word on Google Document.
The words and hyperlinks are retrieved from Google Spreadsheet.

In this case, how about the following modification?

Modified script:

function insertLink() {
  // 1. Retrieve values from Spreadsheet.
  var file,files,folder,folders,newestFileID;
  var filethaythe = DriveApp.getFilesByName('Set Link');
  var ss = SpreadsheetApp.open(filethaythe.next());
  var sheet = ss.getSheetByName('Trang tính1');
  var values = sheet.getDataRange().getValues();

  // 2. Retrieve Google Document.
  folders = DriveApp.getFoldersByName('test3');
  while (folders.hasNext()) {
    folder = folders.next();
    files = folder.getFilesByType("application/vnd.google-apps.document");
    while (files.hasNext()) {
      file = files.next();
      var newestFileID = file.getId();

      // 3. Search words and set hyperlinks on Google Document.
      var currentDoc = DocumentApp.openById(newestFileID);
      var rgtxt = currentDoc.getBody();
      values.forEach(([a, b]) => {
        var s = rgtxt.findText(a);
        while (s) {
          var start = s.getStartOffset();
          s.getElement().asText().setLinkUrl(start, start   a.length - 1, b);
          s = rgtxt.findText(a, s);
        }
      });

      currentDoc.saveAndClose();
      break
    }
  }
}

In order to search the word, the method of findText is used. And, the method of setLinkUrl sets the hyperlink to the searched word.

Modified script:

References: