Google Scripts: Adding Sheets Table to Google Doc

Question

I'm trying to insert a range of data from a Google Sheet into a Google Doc as a table. I'm successful, but it inserts the table at the very end of the Google Doc, and I can't figure out how to insert it in the middle of the doc at a specific place. I also can't figure out how to have it ignore empty rows in the range so that it doesn't insert a table into the doc with empty rows. Any help is appreciated!

Here's a snippet of my code:

var deviceName = sheet.getRange("C2").getValue();    
var parts = sheet.getRange("B14:C28").getValues();

body.replaceText("%DEVICENAME%", deviceName);
var table = body.appendTable(parts);

table.setColumnWidth(0, 200);
table.setColumnWidth(1, 200);

J. G. J. G. · Accepted Answer · 2020-02-03T17:29:03

You should pick up the real data range and use insertTable rather than appendtable.

var deviceName = sheet.getRange("C2").getValue();    
var parts = sheet.getDataRange().getValues();
parts = removeBlanks(parts);

body.replaceText("%DEVICENAME%", deviceName);
var location = 2;
var table = body.insertTable(location, parts);

table.setColumnWidth(0, 200);
table.setColumnWidth(1, 200);

function removeBlanks (removeBlanks){
var result = [];
for (var i=0; i<removeBlanks.length;i++)
    {
    if (removeBlanks[i][0] == 0) continue;
    result.push(removeBlanks[i]);
    }
return result;
}

Play with values for location, I wasn't sure where you actually want it with the given info.

update: added a function to strip out blank rows if the first cell of the row is empty.

Google Scripts: Adding Sheets Table to Google Doc

1 Answers