Home > Back-end >  How to Append a table with specified data from Google Spreadsheet to Google Doc using Google App Scr
How to Append a table with specified data from Google Spreadsheet to Google Doc using Google App Scr

Time:02-14

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

CodePudding user response:

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]]);
  }
}
  • Related