I have a Google Doc that contains placeholders based on A1 notation like %A1%, %B3%, %A3%, and so on.

In a Google Sheet I have values stored in the cell references that match the variable names.

I want to use a script to replace the text in the document from the spreadsheet.

Example Document

I have a %A1% Doc that %B3% placeholders based on %A3% notation

Example Spreadsheet

1 Google
3 A1 contains

Desired Result

I have a Google Doc that contains placeholders based on A1 notation

I have successfully used the following code to make a single replacement but don't know how to loop through all cells in the sheet to make all the replacements.

function myFunction() {
  var myDoc = DocumentApp.openById('docID')
  var docBody = myDoc.getActiveSection()
  var sss = SpreadsheetApp.openById('sheetID'); 
  var x =  sss.getRange("A3:A3").getValues()
  docBody.replaceText('%A3%', x)

2 Answers 2

const docId   = "xyz";
const sheetId = "abc";

function replaceRefByString() {
  const doc = DocumentApp.openById(docId)
  const body = doc.getBody();
  const ss = SpreadsheetApp.openById(sheetId);
  const sheet = ss.getActiveSheet();
  const range = sheet.getDataRange();
  const row = range.getRow();
  const replace = [];

  range.getValues().forEach(function(r, i) {
     r.forEach(function (c, j) {
       c && replace.push(["%"+sheet.getRange(row+i,j+1).getA1Notation()+"%", c])})});
  replace.forEach(value => body.replaceText(value[0], value[1]));
  1. The script gets the populated values from the spreadsheet using getDataRange()
  2. Uses JavaScript Array.prototype.forEach() to iterate through all the row and column values and pushes any that are not null as an array to replace. The two-member array pushed contains:
    1. replace[n][0] is the A1 notation between two percent symbols %A1%
    2. replace[n][1] is the value from the cell
  3. Once all the values have been pushed body.replaceText() is called once for each value in replace.
    1. value[0] is the string to find.
    2. value[1] is the replacement.

To use Google Apps Scripts effectively you should learn Javascript and about arrays.

Google Sheets: A1:A1 and C3:C3 are not proper ways to refer to single cells. Just use A1 and C3, respectively.

Also, if you are getting the value of a single cell use getValue() instead of getValues().

Javascript has many types of loops: for, for..in, do..while, while, Array.prototype.reduce, etc. Looping through Google Sheets data usually requires handling nested arrays. For details see https://developers.google.com/apps-scripts/guides/sheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.