Find and replace text with values from a list/database

Question

I've created a fairly simple script which finds English and replaces it with Spanish words.

function translatesp() {
var body = DocumentApp.getActiveDocument()
.getBody();

body.replaceText('Master Change Log', 'Historial de Cambios');
body.replaceText('Prepared for:', 'Preparado para:'); 
body.replaceText('Git Reference', 'Referencia Git');

}

What I would like to do is have a list somewhere (probably on a separate google sheet) with all of my English and Spanish words in columns, and then change my script to look for everything in the English column and then replace it with the relevant Spanish entry, so that I don't need to keep adding vocabulary to script itself. How would one go about this?

Thanks in advance.

The reason I'm writing this script is because Google translate is terrible. — Chris A

anderspitman anderspitman · Accepted Answer · 2018-01-16T17:56:19

I actually had to do basically this exact thing a while back. I ended up making a helper utility to represent spreadsheets as database tables. I put it on github here:

https://github.com/anderspitman/google-apps-script-database

Copy and paste that file into your script project then use something like the following to use it:

function main() {

  var spreadsheet = SpreadsheetApp.openById('SPREADSHEET_ID_HERE');
  var translationDb = Database.fromSpreadsheet(spreadsheet)
    .getTable('Sheet1');

  var wordInAllLanguages = translationDb.findOne({ English: 'Yes' });

  Logger.log(wordInAllLanguages.Spanish);
}

The spreadsheet looks like this:

Note that Database class is useful for lots of other things as well. Take a look through the code to see the other functions. It's designed to be somewhat similar to interfacing with MongoDB in javascript.

Find and replace text with values from a list/database

1 Answers