谷歌电子表格条件格式脚本(Google Spreadsheet conditional format

我试图找出如何在谷歌电子表格相似，你可以在Excel中通过公式做什么使用条件格式。

我想单元格A2改变为绿色，如果电池O2有“X”的价值，这将在两列完成一路下滑。 我知道这将需要一个脚本。

我跑过一个链接，是类似的，但我不知道如何调整它来满足我的需求。 这是不是可以做？

链接： https://webapps.stackexchange.com/questions/16745/google-spreadsheets-conditional-formatting

Answer 1:

这里有一个脚本，你可以用做你所描述的：

function formatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var columnO = sheet.getRange(2, 15, sheet.getLastRow()-1, 1);
  var oValues = columnO.getValues();

  for (var i = 0; i < oValues.length; i++) {
    if (oValues[i][0] == 'X') {
      sheet.getRange(i + 2, 1, 1, 1).setBackgroundColor('green');
    }
  }
}

Answer 2:

在新的谷歌片，这不再需要的脚本。

相反，在条件格式，选择选项“自定义公式”，并把像一个值=O2="X" -或者实际上返回一个true / false值的表达式。

从我所知道的，在这些自定义脚本列出的参考文献都有点怪异，并应用如下...

如果这是你的选择范围内的单元格，然后将其更改为“这是被突出显示单元格”。

如果这是你的选择范围之外的单元格，然后它改为“那个位置，再加上一个偏移量与从当前单元格选定范围的左上方偏移”。

也就是说，如果您的范围为A1:B2 ，然后上面会相同如下对每个小区设置单独的格式：

A1 =O2="X"
A2 =O3="X"
B1 =P2="X"
B2 =P3="X"

您还可以指定固定的参考，像=$O$2="X" -这将检查特定小区O2在所选范围内的所有细胞。

Answer 3:

(Feb 2017) As mentioned in another answer, Google Sheets now allows users to add Conditional Formatting directly from the user interface, whether it's on a desktop/laptop, Android or iOS devices.

Similarly, with the Google Sheets API v4 (and newer), developers can now write applications that CRUD conditional formatting rules. Check out the guide and samples pages for more details as well as the reference docs (search for {add,update,delete}ConditionalFormatRule). The guide features this Python snippet (assuming a file ID of SHEET_ID and SHEETS as the API service endpoint):

myRange = {
    'sheetId': 0,
    'startRowIndex': 1,
    'endRowIndex': 11,
    'startColumnIndex': 0,
    'endColumnIndex': 4,
}

reqs = [
    {'addConditionalFormatRule': {
        'index': 0,
        'rule': {
            'ranges': [ myRange ],
            'booleanRule': {
                'format': {'textFormat': {'foregroundColor': {'red': 0.8}}}
                'condition': {
                    'type': 'CUSTOM_FORMULA',
                    'values':
                        [{'userEnteredValue': '=GT($D2,median($D$2:$D$11))'}]
                },
            },
        },
    }},
    {'addConditionalFormatRule': {
        'index': 0,
        'rule': {
            'ranges': [ myRange ],
            'booleanRule': {
                'format': {
                    'backgroundColor': {'red': 1, 'green': 0.4, 'blue': 0.4}
                },
                'condition': {
                    'type': 'CUSTOM_FORMULA',
                    'values':
                        [{'userEnteredValue': '=LT($D2,median($D$2:$D$11))'}]
                },
            },
        },
    }},
]

SHEETS.spreadsheets().batchUpdate(spreadsheetId=SHEET_ID,
        body={'requests': reqs}).execute()

In addition to Python, Google APIs support a variety of languages, so you have options. Anyway, that code sample formats a Sheet (see image below) such that those younger than the median age are highlighted in light red while those over the median have their data colored in red font.

---

PUBLIC SERVICE ANNOUNCEMENT

The latest Sheets API provides features not available in older releases, namely giving developers programmatic access to a Sheet as if you were using the user interface (conditional formatting[!], frozen rows, cell formatting, resizing rows/columns, adding pivot tables, creating charts, etc.).

If you're new to the API & want to see slightly longer, more general "real-world" examples of using the API, I've created various videos & related blog posts:

• Migrating SQL data to a Sheet plus code deep dive post
• Formatting text using the Sheets API plus code deep dive post
• Generating slides from spreadsheet data plus code deep dive post

As you can tell, the Sheets API is primarily for document-oriented functionality as described above, but to perform file-level access such as uploads & downloads, imports & exports (same as uploads & downloads but conversion to/from various formats), use the Google Drive API instead. Examples of using the Drive API:

• Exporting a Google Sheet as CSV (blog post only)
• "Poor man's plain text to PDF" converter (blog post only) (*)

(*) - TL;DR: upload plain text file to Drive, import/convert to Google Docs format, then export that Doc as PDF. Post above uses Drive API v2; this follow-up post describes migrating it to Drive API v3, and here's a video combining both "poor man's converter" posts.

文章来源: Google Spreadsheet conditional formatting script