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?
You want to do the following:
ROLES
and NAMES
from a Sheet.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]));
}
{{your-role-name}}
. [
is a special character for regex, so I think using {{
is more appropriate for this situation.