Search code examples
google-apps-scriptgoogle-sheetsgoogle-docs-api

Google sheets/doc - table customisations - banding

slowly learning how to do some basic coding using google apps scripts on sheets and docs.

Currently creating a dynamic Report generator based off of information gathered in a sheet but have stumbled when trying to customise a table going from Sheets to Docs.

I'm trying to apply banding (alternateing row colours) to my table in docs to make it look more professional and clean, I've tried looking in numerous places but cant seem to find anything that works or that I can atleast make sense of.

Creating the file etc has been no issue and surprisingly easy I've added the code for context.

function reportgenerator() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SHEETNAME');

//name for new file
  var firstname = sheet.getRange(2,1).getValue();
  var reportname = firstname+"'s Report";

//creating document
 const googleDocTemplate = DriveApp.getFileById('FILEID');
 const destinationFolder = DriveApp.getFolderById('FOLDERID');
 const copy = googleDocTemplate.makeCopy(reportname,destinationFolder);
 const doc = DocumentApp.openById(copy.getId());
 const body = doc.getBody();

I have a feeling my problem might stem from the following section of code, the range I am pulling data from is quite dynamic and the number of rows that will be in the table could be anything from 20 to 60+, hence the use of getDataRegion.


// data range for table
var tablerange = sheet.getRange(25,9).getDataRegion();
var tablevalues = tablerange.getValues();

// table style
  var tablestyle = {};
  tablestyle[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  tablestyle[DocumentApp.Attribute.FONT_SIZE] = 11;
  tablestyle[DocumentApp.Attribute.BORDER_COLOR] = '#ffffff';
  tablestyle[DocumentApp.Attribute.BACKGROUND_COLOR] = '#cccccc';

// replace text with table
  var rgel = body.findText('{{answertable}}');
  var element = rgel.getElement();
  var childIndex = body.getChildIndex(element.getParent());
    
  body.getChild(childIndex).asText().setText('');
  body.insertTable(childIndex,tablevalues).setAttributes(tablestyle).setColumnWidth(2,55);

  doc.saveAndClose();
}

All I have managed to do is color the background of the text, which doesnt suit my requirements, as stated before I wish to apply banding to the entire row and then every second one there after, I am unfamiliar with how to use "for" and creating a loop to read and color in the cells/rows somehow? however I have am very unfamiliar with using "for".

there is a header too but same deal with styling and isolating it to be different from the other cells has been an issue for me.

Any help would be much appreciated, save me from hitting my head against a brickwall anymore.

Solution

  • I believe your goal as follows.

    • You want to achieve the following situation on Google Document by using the values retrieved from Google Spreadsheet using Google Apps Script.

      enter image description here

    In this case, at first, creates the table. And then, the background colors of each row are changed.

    Sample script:

    Please set spreadsheetId, documentId and the sheet name.

    function myFunction() {
  var spreadsheetId = "###";  // Please set the Spreadsheet ID.
  var documentId = "###";  // Please set the Document ID.

  var tablevalues = SpreadsheetApp.openById(spreadsheetId).getSheetByName("Sheet1").getDataRange().getDisplayValues();
  var body = DocumentApp.openById(documentId).getBody();  // Or please use your "body".
  var tablestyle = {};
  tablestyle[DocumentApp.Attribute.FONT_FAMILY] = 'Arial';
  tablestyle[DocumentApp.Attribute.FONT_SIZE] = 11;
  tablestyle[DocumentApp.Attribute.BORDER_COLOR] = '#ffffff';
  var rgel = body.findText('{{answertable}}');
  var element = rgel.getElement();
  var childIndex = body.getChildIndex(element.getParent());
  body.getChild(childIndex).asText().setText('');
  var table = body.insertTable(childIndex, tablevalues).setAttributes(tablestyle).setColumnWidth(2, 55);
  for (var r = 0; r < table.getNumRows(); r++) {
    var row = table.getRow(r);
    for (var c = 0; c < row.getNumCells(); c++) {
      if (r == 0) {
        row.getCell(c).setBackgroundColor('#008000');
      } else if (r % 2 == 0) {
        row.getCell(c).setBackgroundColor('#cccccc');
      }
    }
  }
}
    • In this sample, #008000 and #cccccc are used as the colors. So please modify the color for your actual situation.

    References: