As title, I’ve tried to append a table to Google Docs from Google Spreadsheet using GAS. So far, the post and some scripts I can find is about appending the whole sheet, not the selected or specified row or column. Below is the snippet. What should I do to make the snippet meet my need? Please advice.
p.s. the exception message: Exception: The parameters (number[]) don’t match the method signature for DocumentApp.Body.appendTable. would pop up after i execute the script.
function get_insertion_and_deletion_value(fileID) { var url="MY URL"; var SpreadSheet = SpreadsheetApp.openByUrl(url); var ss = SpreadSheet.getSheetByName("LOG"); var values = ss.getDataRange().getValues(); var reportHeader = "Report Header"; var ad= DocumentApp.openById("MY ID"); var body = ad.getBody(); var info_table=[]; for(var i=0, iLen=values.length; i<iLen; i++) { if(values[i][0]=="MYID") { var delete_string=ss.getRange(i+1,3).getValue(); var insert_string=ss.getRange(i+1,7).getValue(); var error_type=ss.getRange(i+1,13).getValue(); var correction_content=ss.getRange(i+1,14).getValue(); info_table.push(ss.getRange(i+1,3).getValue(),ss.getRange(i+1,7).getValue(),ss.getRange(i+1,13).getValue(),ss.getRange(i+1,14).getValue()) } } body.insertParagraph(0, reportHeader).setHeading(DocumentApp.ParagraphHeading.HEADING1); table = body.appendTable(info_table); var tableHeader = table.getRow(0); tableHeader.editAsText().setBold(true).setForegroundColor('#ffffff'); var getCells = tableHeader.getNumCells(); for(var i = 0; i < getCells; i++) { tableHeader.getCell(i).setBackgroundColor('#BBB9B9'); } return info_table; }
Advertisement
Answer
Might I suggest you change your for loop as shown. I deleted the unused variables. 1st your need to construct rows just like Spreadsheet setValues() so I’ve enclosed the list of values within []. 2nd you already have the values so all these ss.getRange(i+1,3).getValue() are redundant and cause another call to the server. Remember column 3 is index 2 in the values array. So I’ve just used the values you got from getDataRange().getValues().
for(var i=0, iLen=values.length; i<iLen; i++) { if(values[i][0]=="MYID") { info_table.push([values[i][2],values[i][6],values[i][12],values[i][13]]); } }