Google电子表格:所有粗体单元格的总和 [英] Google spreadsheet: Sum of all bold cells

问题描述

我正在尝试学习Google Spreadsheet中的脚本，并且我已经获得了一些简单的脚本可以使用，但这确实是一个痛苦.

I'm trying to learn scripting in Google Spreadsheet, and I have gotten some simple scripts to work, but this one is a real pain.

我想制作一个脚本，该脚本使用onEdit()函数更新特定的单元格，以显示电子表格中所有粗体值的总和.

I want to make a script that uses the onEdit() function to update a specific cell to show the sum of all bold values in the spreadsheet.

Fx:

1 2个 3

4

然后该单元格的值为(3 + 4)7.

Then the cell would have a value of (3+4) 7.

希望有道理！

推荐答案

有些迟了，但是值得一个答案，我一直在研究类似的问题.

It's a bit late, but it's worth an answer and I've been working on a similar question.

要使用的公式是:

=sumIfBold(A1:B4,COLUMN(A1), ROW(A1))

脚本为:

/**
 * Sums cell values in a range if they are bold. The use of startcol and startrow
 * is to enable the formula to be copied / dragged relatively in the spreadsheet.
 * 
 * @param  {Array.Array} range    Values of the desired range
 * @param  {int} startcol The column of the range
 * @param  {int} startrow The first row of the range
 * 
 * @return {int}          Sum of all cell values matching the condition
 */
function sumIfBold(range, startcol, startrow){
  // convert from int to ALPHANUMERIC 
  // - thanks to Daniel at http://stackoverflow.com/a/3145054/2828136
  var start_col_id = String.fromCharCode(64 + startcol);
  var end_col_id = String.fromCharCode(64 + startcol + range[0].length -1);
  var endrow = startrow + range.length - 1

  // build the range string, then get the font weights
  var range_string = start_col_id + startrow + ":" + end_col_id + endrow
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var getWeights = ss.getRange(range_string).getFontWeights();

  var x = 0;
  var value;
  for(var i = 0; i < range.length; i++) {
    for(var j = 0; j < range[0].length; j++) {
      if(getWeights[i][j].toString() == "bold") {
        value = range[i][j];
        if (!isNaN(value)){
          x += value;
        }
      }
    }
  }
  return x;
}

这篇关于Google电子表格:所有粗体单元格的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！