15

In Microsoft Word, it is possible to use very simple formula e.g. =SUM(ABOVE) to display the total of a column or =SUM(LEFT) etc. Is it possible to do something similar in Google Docs? Obviously this is possible in Google Spreadsheets, but I need to do it within a table in a document.

9

At this time Google Docs (documents) doesn't include a feature to do calculations.

An alternative is to use an add-on or a Google Apps Script to extend the built-in features of Google Docs.

References
Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help

3

To illustrate the usage of Apps Script for such purpose, I wrote a function that sums selected values in a document: either in a table, or in a paragraph, or in several paragraphs, etc.

It is invoked by menu command "Sum selected", found under "Custom"; this menu item is created when the document is opened. The text of election is split by whitespace, and then each piece is interpreted as a number (if possible). Such numbers are added and the result is displayed to the user by an alert dialog. (I considered inserting it into the document, but there isn't a natural place for it: when a user is selecting text, their cursor position is lost.)

This is a one-time calculation; if the values are changed, one would need to perform it again.

function onOpen() {
  DocumentApp.getUi().createMenu('Custom').addItem('Sum selected', 'sum').addToUi();  
}

function sum() {  
  var selection = DocumentApp.getActiveDocument().getSelection();
  if (selection) {
    var elements = selection.getRangeElements();
    var s = 0;
    for (var i = 0; i < elements.length; i++) {
      var element = elements[i];
      var text = element.getElement().editAsText();
      if (text) {
        var str = text.getText();
        if (element.isPartial()) {
          str = str.slice(element.getStartOffset(), element.getEndOffsetInclusive() + 1);
        }
        var pieces = str.split(/\s+/);
        for (var j = 0; j < pieces.length; j++) {
          s += (parseFloat(pieces[j], 10) ? parseFloat(pieces[j], 10) : 0);
        }
      }
    }
    DocumentApp.getUi().alert(s);
  }
}
0

Using the add-on Reversible Formulas - Sketch-n-Script, you can add formulas to display a table and also to perform arbitrary JavaScript computation on it.

Write in your document:

expenses = ([10, 20, 30])
incomes = ([30, 20, “”])

=table([["Expenses", "Income"]].concat(rotate([expenses, incomes])))

Totals

=table([["Expenses", "Income"]].concat([[sum(expenses),sum(incomes)]]))

Click on "Display values". You should obtain the following - I added bold to 'Totals':

Rendering

Url of the add-on: https://chrome.google.com/webstore/detail/formulas-for-google-docs/kgjbgmonlnhglokmfnpnklegakfknbeh

Disclaimer: I am the author of this add-on. Disclaimer: As of now, it's not yet possible to modify the rendered tables visually. I'm working on it.

0

I took user79865's code and made some changes.

This code can evaluate any simple equation from a selected text:

function onOpen() {  
  DocumentApp.getUi().createMenu('Custom')  
  .addItem('Evaluate', 'evaluate').addToUi()  
  .addToUi();
}

function evaluate() {      
  var selection = DocumentApp.getActiveDocument().getSelection();  
  if (selection) {  
    var element = selection.getRangeElements()[0];  
    var text = element.getElement().editAsText();  
    if (text) {  
      var str = text.getText();  
      if (element.isPartial()) {  
        str = str.slice(element.getStartOffset(), element.getEndOffsetInclusive() + 1);  
      }  
      var val = eval(str);  
      val = +val.toFixed(2)  
      DocumentApp.getUi().alert("Equation: " + str + "\nResult: " + val);                             
      //text.appendText('\nAppended text.');  
    }  
  }  
}

It's also possible to uncomment the last line and the code will write the answer at the document.

Operators that I’ve used and worked well:

  • + Addition
  • Subtraction
  • * Multiplication
  • / Division
  • ** Exponentiation
  • ( ) Group numbers in the order of operations
0

I like Rogério's answer, and will like it even better once my Apps Script skills get good enough so that I can figure out how to make the result populate where I want it to in the Google Doc.

Rogério's solution, however, doesn't answer Jonathan Day's question. I've also been looking for a similar solution and have come across something that's only partial, but better than nothing. I found it on here: Functions on a Google Documents Table.

I was struggling with the code as shown on the author's blog, so I contacted him and he was gracious enough to send me a fuller version of the code to test. Here's the script that I've implemented on this doc:

function onOpen(e) {
  var menu = DocumentApp.getUi().createAddonMenu();
  menu.addItem("Sum Table", "sumTable");
  menu.addToUi();
}

function sumTable() {
    // First table is “table[0]”.
    var table_index = 0;
    // First row is “row[0]”.
    // Starting row offset for skipping a header.
    var row_start_offset = 1;
    // First column is “column[0]”.
    var column_index = 1;

    // Get document body.
    var doc = DocumentApp.getActiveDocument();
    var body = doc.getBody();

    // Get the tables.
    var tables = body.getTables();
    // Find out how many rows are in the table.
    var numRows = tables[table_index].getNumRows();

    // Initialize variables.
    var row;
    var val;
    var total = 0.0;
    // Starting from the row offset, loop over all rows until the last.
    for(row = row_start_offset; row<numRows-1; row++) {
        // Get the value of the cell.
        val = tables[table_index].getCell(row, column_index).getText();
        // Skip the "$" if present.
        if (val[0] == '$') {
        val = val.slice(1);
        }
        // If there is a value in the cell.
        if(val) {
        total += parseFloat(val);
        }
    }

    tables[table_index].getCell(numRows-1,column_index).setText("$"+total.toString());
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.