Search code examples
google-apps-scriptgoogle-sheetsgoogle-docs

Insert the link into the keyword in the sheet file

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
      

    }
  
};
}
Solution

  • 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.

    References: