Skip to content

Instantly share code, notes, and snippets.

@erickoledadevrel
Last active April 9, 2022 16:48
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save erickoledadevrel/e6f2517ce077c291d6a4bfd768e470c9 to your computer and use it in GitHub Desktop.
Save erickoledadevrel/e6f2517ce077c291d6a4bfd768e470c9 to your computer and use it in GitHub Desktop.
function onOpen() {
DocumentApp.getUi().createMenu('Demo')
.addItem('Select Spreadsheet', 'selectSpreadsheet')
.addItem('Update Data', 'updateData')
.addToUi();
}
function selectSpreadsheet() {
var result = DocumentApp.getUi().prompt('Enter the ID of the spreadsheet:');
var spreadsheetId = result.getResponseText();
PropertiesService.getDocumentProperties().setProperty('spreadsheetId', spreadsheetId);
}
function updateData() {
var doc = DocumentApp.getActiveDocument();
var spreadsheetId = PropertiesService.getDocumentProperties().getProperty('spreadsheetId');
var ss = SpreadsheetApp.openById(spreadsheetId);
findNewTags(doc);
doc.getNamedRanges().forEach(function(namedRange) {
// namedRange = doc.getNamedRanges()[];
var name = namedRange.getName();
var value = ss.getRangeByName(name).getValue().toString();
var rangeElement = namedRange.getRange().getRangeElements()[0];
if (rangeElement.isPartial()) {
var text = rangeElement.getElement().asText();
text.insertText(rangeElement.getEndOffsetInclusive() + 1, value);
text.deleteText(rangeElement.getStartOffset(), rangeElement.getEndOffsetInclusive());
} else {
throw 'TODO';
}
});
}
function findNewTags(doc) {
var body = doc.getBody();
var match = null;
while (match = body.findText('{{.*?}}', match)) {
var element = match.getElement();
var range = doc.newRange();
var name;
if (match.isPartial()) {
var text = match.getElement().asText();
range.addElement(text, match.getStartOffset(), match.getEndOffsetInclusive());
name = text.getText().substring(match.getStartOffset() + 2, match.getEndOffsetInclusive() - 1);
} else {
throw 'TODO';
}
doc.addNamedRange(name, range);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment