In a Google Doc, copy tables and paste data into them from values in a Google Sheet
I have a Google Doc that I would like to use as a template for my project. I have multiple different tables in it, but there are two of them that I want to make copies of using Apps Script.
I would like to read the range of data in Column A of a Google Sheet and then create that many copies of Table 1 in the Google Doc. I would then like to repeat that same process for Column B of the Google Sheet and Table 2 in the Google Doc template.
I have the code to work for the first Table, but it always places the copies at the bottom of the document. I would like for it to be located immediately after the first one. I haven't been able to figure out how to do the process for the Column B and the Table 2.
I started with the below code, but it pastes the tables at the bottom of the document.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var pasteTestSheet = ss.getSheetByName('Test');
var values = pasteTestSheet.getRange('A1:A2').getValues();
var docId = pasteTestSheet.getRange('D2').getValue();
var doc = DocumentApp.openById(docId);
var table1 = doc.getBody().getTables()[0];
var body = doc.getBody();
for ( var i=0; i<values.length; i++) {
var copy1 = table1.copy();
var table2var = i+1;
copy1.getCell(0,0).setText(values[i][0]);
table1.getParent().appendTable(copy1);
}
}
I've been banging my head into the wall for a while on this and would appreciate any help. Thanks!
Here is the link to the test google doc template:
Here is the link to the test google sheet template.
Here are pictures to help. I bolded the text that will have placeholders fthat I would like to replace from the google sheet cells.
Here is an example of copying the table within the document. I first make a copy of the template using DriveApp so the template can be reused in the future. Then I create a seperate table for each entry in column A. And then for column B. To insert after the existing table I need to getChildIndex() of the current table and add 1.
I've modified my original answer to reflect the format of your spreadsheet.
And in answer to your question below, no. I typically organize my data so that I only need to use 1 getValues(), see Best Practices. I then now how to index into that array to get the value or values I want. Since you have 2 lists, one in column A and one in column H, since they can be different lengths I use the if to see if there are any more items in each list.
Code.gs
function copyTables() {
try {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spread.getSheetByName('Test');
let values = sheet.getRange(3,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
let doc = DriveApp.getFileById("1eoLxiQ83L0y-NzzBmk8WTrd_TgO-v8r0-1Ks_530iXE")
doc = doc.makeCopy();
doc = DocumentApp.openById(doc.getId());
let body = doc.getBody();
let tables = body.getTables();
// Table 1
let table = tables[0];
let copy = table.copy();
values.some( (row,index) => {
table.replaceText("{{Item Number Placeholder}}",row[1]); // start in column B
table.replaceText("{{Title Placeholder}}",row[2])
table.replaceText("{{Item ID Placeholder}}",row[3])
table.replaceText("{{Item Descriiption Placeholder}}",row[4]);
table.replaceText("{{Item Picture Placeholder}}",row[5]);
// either end of rows or entry in column is blank
if( ( index < values.length-1 ) && ( values[index+1][0] === "" ) ) return true;
table = body.insertTable(body.getChildIndex(table)+1,copy.copy())
return false;
}
);
// Table 2
table = tables[1];
copy = table.copy();
values.some( (row,index) => {
table.replaceText("{{Item Number Placeholder}}",row[8]); // starts in column I
table.replaceText("{{Title Placeholder}}",row[9])
table.replaceText("{{Item ID Placeholder}}",row[10])
table.replaceText("{{Item Descriiption Placeholder}}",row[11]);
table.replaceText("{{Item Picture Placeholder}}",row[12]);
// either end of rows or entry in column is blank
if( ( index < values.length-1 ) && ( values[index+1][7] === "" ) ) return true;
table = body.insertTable(body.getChildIndex(table)+1,copy.copy())
return false;
}
);
}
catch(err) {
console.log(err);
}
}
Results
References