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

dynamic values in websites and docs


Let's say my company has a website and it includes various text. One of the texts it includes is the name of my Director of HR. His/her name could appear 20 or 30 times in various pages and documents on our website. And, should there be a change and I hire a new Director of HR, someone'd have to do manually change those 20 or 30 values.

There's got to be a better way to do that. One option is a scrub the website of personal data in all instances but one, "Contact the Director of HR," and then have the one value "the director of HR is..." and hyperlink all the instances of the Director of HR to the Staff page. But I'm interested in dynamic values.

To begin with, let's say I have this Google Doc and this Google Sheet. How would I get the value on the Google Doc to change when I change the value on the Google Sheet?


Solution

  • You want to do the following:

    • Get a list of ROLES and NAMES from a Sheet.
    • For each ROLE/NAME pair, look for a specific value in a Google Doc, and replace that value with the corresponding NAME.

    You can use replaceText(searchPattern, replacement) to achieve this. It could be something like this:

    function findAndReplace() {
      // Get "Sheet1", where data is:
      var sheet = SpreadsheetApp.openById("your-spreadsheet-id").getSheetByName("Sheet1");
      // Get the document body:  
      var doc = DocumentApp.openById("your-doc-id").getBody();
      // Get the "ROLE/NAME" values:
      var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
      // For each "ROLE/NAME" pair, replace text:
      values.forEach(row => body.replaceText("{{" + row[0] + "}}", row[1]));
    }
    

    Note:

    • In this sample, the values to look for in the Doc are built using each corresponding role, with this pattern: {{your-role-name}}. [ is a special character for regex, so I think using {{ is more appropriate for this situation.

    Reference: