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

Google Docs table - how to format table (cell) and open document in browser

With Google Sheets I created a calculation table which I want to copy to a quote template in Google Docs with Google Apps Script. For this I use the solution found here in this question: Using Google Apps Script to Copy from Google Sheets Data Table to Google Documents Table.

I use (literally exactly) the first sample script, without taking care of column width, as this is not an issue for me.
The copying works great, but I still have a few problems:

  1. all the cell values are copied as text, so in the Google Docs table I have to reformat some values in Euro
  2. in the Google Docs table all the cells are left aligned, I need to right align the columns with Euro values
  3. the script uses "body.appendTable(values)" to insert table data at the end of the document, but how can I determine where in the document I want to place the table?
  4. once created the new document from the template I would like to open it directly in the browser

My script is bound to the Google Sheet document, where I start the script from a custom menu.
You can see the calculation sheet here and the template here.

Hope to find someone who can help.


EDIT to answer questions from comments:

  1. Hey J.G., maybe the problem wasn't explained well enough or I am not smart enough to handle the solutions you proposed:
    setNumberFormat() worked for me only on a spreadsheet, but I have to format the table in Google Docs document.
    With the insertTable(childIndex, table) I was able to insert the table only at the beginning or the end of the document. What am I missing?

  2. OK, it places the table on line 5 in this case, tried also many other indexes and was able to come to the position I want it to be. But this is not reliable enough for me, because if the template changes (and it will change!) I have to find the right index again and change the script. Or is there a possibility to search for a keyword in the doc and get it's childIndex via script?

Solution

  • Issues 1-3 can be easily solved. Here is the working script:

    Code:

    function myFunction() {
  // Get Google Sheet data
  var ssId = 'sample sheet id';
  var docId = 'sample doc id';
  var ss = SpreadsheetApp.openById(ssId);  // Please set the Spreadsheet ID.
  var sheet = ss.getSheetByName("DataFilter");
  var range = sheet.getRange(4, 1, 1, 5).getDataRegion(SpreadsheetApp.Dimension.ROWS);
  // to include euro signs
  var values = range.getDisplayValues();
  var backgroundColors = range.getBackgrounds();
  var styles = range.getTextStyles();

  // Position to paste data in Google Docs
  var body = DocumentApp.openById(docId).getBody();
  var numElements = body.getNumChildren();
  // if 'Table_goes_here' is not found, append to bottom
  var index = numElements;

  // var index = body.getChildIndex(body.findText('Table_goes_here').getElement().getParent());
  // index wasn't found using the code above for some reason, so doing loop instead
  for (var i = 0; i < numElements; i++) {
    var child = body.getChild(i);
    if (child.asText().getText() == 'Table_goes_here') {
      index = i;
      // remove child
      body.removeChild(child);
      break;
    }
  }
  // then insert your table
  var table = body.insertTable(index, values);
  table.setBorderWidth(0);
  for (var i = 0; i < table.getNumRows(); i++) {
    for (var j = 0; j < table.getRow(i).getNumCells(); j++) {
      var obj = {};
      obj[DocumentApp.Attribute.BACKGROUND_COLOR] = backgroundColors[i][j];
      obj[DocumentApp.Attribute.FONT_SIZE] = styles[i][j].getFontSize();
      if (styles[i][j].isBold()) {
        obj[DocumentApp.Attribute.BOLD] = true;
      }
      // if euro sign is found in text, align right
      if (table.getRow(i).getCell(j).getText().includes('€')) {
        table.getRow(i).getCell(j).getChild(0).asParagraph().setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
      }
      table.getRow(i).getCell(j).setAttributes(obj);
    }
  }
}

    start of table:

    start

    end of table:

    end

    Note:

    • Issue 4 is a tricky one. Apps Script cannot easily communicate with the browser (afaik) since this runs on the server side, not client. You can try showing the document on a WebApp instead.

    Resources: