CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-docs
Craig
Craig

Reputation: 55

Google Scripts: Adding Sheets Table to Google Doc

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);

Upvotes: 0

Views: 925

Answers (1)

J. G.
J. G.

Reputation: 1832

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.

Upvotes: 1

Related Questions