[Goal]
I want to be able to copy a table from a Spreadsheet and paste it to a Google Docs agenda document that is used for team meetings. The exact steps are as follows.
- The user will use the custom menu on the Google Docs agenda file to run the script.
- The script will copy the all the body content from another separate Google Docs template file.
- It'll then paste everything starting from the 4th line from the top of the agenda file.
Example:
- The script will copy a table from a Spreadsheet (in this example, range A5:G17).
- The script will then paste the table into the agenda file in the exact location. Example:
[Issue]
However, the copied table from the spreadsheet executed by the script isn't properly formatted in the agenda template. Here are the examples.
What I DON'T want: This is done through the script.
What I do want: I had to manually copy the table from the spreadsheet and paste it manually to the agenda doc. However, I'd prefer having the script do it.
[My Code so far]
function onOpen() {
initMenu();
}
function initMenu() {
const ui = DocumentApp.getUi();
const menu = ui.createMenu('Custom Functions');
// updateMainData function
menu.addItem('Add New Week', 'addNewWeek');
menu.addToUi();
}
function addNewWeek() {
const doc = DocumentApp.getActiveDocument();
const templateDoc = DocumentApp.openById("12COMKRpQT-sut-esvYR9B8KlrSptr5JcwP6Min2uxMU");
const templateBody = templateDoc.getBody();
var templateTotalElements = templateBody.getNumChildren();
// Modifying Tempalte Date
var index1 = templateBody.getChild(1).getText();
const templateDate = new Date(templateBody.getChild(1).getText());
const incrementDate = new Date(templateDate.setDate(templateDate.getDate() + 7));
const convertDateFormat = Utilities.formatDate(incrementDate, "JST", "yyyy-MM-dd");
templateBody.replaceText(index1, convertDateFormat);
for (var i = 0; i < templateTotalElements; i++) {
var body = doc.getBody();
var element = templateBody.getChild(i).copy();
var type = element.getType();
if (type == DocumentApp.ElementType.PARAGRAPH) {
body.insertParagraph(3 + i, element);
} else if (type == DocumentApp.ElementType.TABLE) {
body.insertTable(3 + i, element);
} else if (type == DocumentApp.ElementType.LIST_ITEM) {
body.insertListItem(3 + i, element);
}
}
const mtgSheet = SpreadsheetApp.openById("1MMSGNt4tPZmTLEsOMNA9i1TI0duY88Ey9gJHMq0G9sU").getSheetByName("Sheet1");
const mtgRange = mtgSheet.getRange("A1:G13").getDataRegion(SpreadsheetApp.Dimension.ROWS);
const mtgValues = mtgRange.getDisplayValues();
const mgtBackgroundColors = mtgRange.getBackgrounds();
const mtgStyle = mtgRange.getTextStyles();
var body = doc.getBody();
var table = body.insertTable(8, mtgValues);
table.setBorderWidth(0);
for (var i = 0; i < table.getNumRows(); i++) {
for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
var obj = {};
obj[DocumentApp.Attribute.BACKGROUND_COLOR] = mgtBackgroundColors[i][j];
obj[DocumentApp.Attribute.FONT_SIZE] = mtgStyle[i][j].getFontSize();
if (mtgStyle[i][j].isBold()) {
obj[DocumentApp.Attribute.BOLD] = true;
}
table.getRow(i).getCell(j).setAttributes(obj);
}
}
}
[References that I found]
I tried using the methods listed here, which got me very close, however, I don't know what to do next.
