Can Google Sheet data be inserted into a Google Doc script?

Question

I am a VERY novice code writer, and a teacher, so I want to write a Google Doc script to find incorrect words in a paragraph in a Google Doc, and replace them with correct words. However, I want the correct and incorrect words taken from a Google Sheet worksheet.

I have some code (see below) which finds and replaces words (Hi -> Hello), but this is not user friendly.

Find and Replace code

I am experimenting with spreadsheets.value.get but it is beyond me at the moment. Am I on track or no where near? Any ideas? Thanks.

I think that providing your script as a string instead of an image will help users think of your solution. — Tanaike
Yes you can access an Spreadsheet with SpreadsheetApp.openById() from a Google Doc Script. — Cooper

Cameron Cameron · Accepted Answer · 2019-06-06T00:23:56

To answer your question, I would rethink even pulling in the values from a spreadsheet. You want to use a javascript object. An object is a place to store key-value pairs. They are super efficient at both storing and retrieving data.

If possible, I would store the words and replacements as key-value pairs in an object, like this:

var myObject = {
  "hi": "hello",
  ...
}

If you try to iterate through each row of a spreadsheet to find the matching correction, your code is going to take a long time as your document and spreadsheet get longer.

You could store the words and replacements in a sheet, retrieve the range of cells you want, and put the keys and values into an object, like this:

(Column A is key ["hi"], Column B is value ["Hello"])

var spreadsheet = SpreadsheetApp.openById('SPREADSHEET ID HERE');
var sheet = spreadsheet.getSheetByName('SHEET NAME HERE')
var range = sheet.getRange('A1:B100');
var values = range.getValues();

var myObject = {};

for (row in values) {
  myObject[values[row][0]] = values[row][1];
}

myObject would look something like this:

{
  "hi": "hello",
  ...
}

Now, just loop through your object and replace like you did in the screenshot you attached, like this:

for (key in myObject) {
  body.replaceText(key, myObject[key]);
}

Also, instead of simply searching for the key and replacing, you might want to use a regex pattern. The current way of doing it will not avoid changing "this" into "thellos". The pattern below will only identify words that match the key and not letter combinations anywhere in the text.

for (key in myObject) {
  body.replaceText('/\b' + key +'\b/gi', myObject[key]);
}

Can Google Sheet data be inserted into a Google Doc script?

1 Answers