Google Sheets / Сменить формат ячейки если значения в ней совпадает с переменной

Условное форматирование создается запросом addConditionalFormatRule

Примерно так выглядит отправка запроса:

  res = await api.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: {
      requests: [
        {
          addConditionalFormatRule: {
            rule: {
              ranges: [
                {
                  sheetId: 0,
                  startColumnIndex: 2,
                  endColumnIndex: 3,
                }
              ],
              booleanRule: {
                condition: {
                  type: "TEXT_CONTAINS",
                  values: [{ userEnteredValue: "foo" }]
                },
                format: {
                  textFormat: {
                    bold: true,
                    foregroundColor: { blue: 1 }
                  },
                  backgroundColor: { red: 1, green: 1 }
                }
              }
            },
            index: 0
          }
        },
      ],
    },
  })

Значения для spreadsheetId и sheetId берем из адресной строки самой таблицы: spreadsheetId это хэш-строка, а sheetId это gid (для первого листа обычно равен 0).

В правиле указываем диапазоны ячеек (набор GridRange) и одно из двух правил - либо booleanRule, либо gradientRule (нам будет достаточно первого).

BooleanRule разбито на две части: необходимое условие condition и применяемый при его выполнении формат ячейки format. Возможные типы условий перечислены здесь (в примере использован TEXT_CONTAINS), а параметры форматирования здесь.

Вот полный код демонстрационной программы (используется легковесный пакет @googleapis/sheets). При первом запуске в консоль выдается url страницы авторизации и ожидается ввод кода с последней страницы, после запроса токен сохраняется в файле token.json и последующие запуски используют его напрямую (если токен уже есть, можно скопировать его и не шаманить с командной строкой).

const fs = require('fs/promises')
const readline = require('readline/promises') // node 17+
const { sheets, auth } = require('@googleapis/sheets')
const { installed } = require('./credentials.json')

async function runSample(spreadsheetId) {

  let client
  try {
    const { client_secret, client_id, redirect_uris } = installed
    client = new auth.OAuth2(client_id, client_secret, redirect_uris[0])
  } catch (error) {
    console.log('Error loading client credentials file:', error)
    return
  }

  try {
    // Check if we have previously stored a token.
    const token = await fs.readFile('token.json')
    client.setCredentials(JSON.parse(token))
  } catch (error) {
    if (error.code === 'ENOENT') {
      const token = await getNewToken(client)
      client.setCredentials(token)
    } else {
      console.error(error)
      return
    }
  }

  const api = sheets({ version: "v4", auth: client })

  res = await api.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: {
      requests: [
        {
          addConditionalFormatRule: {
            rule: {
              ranges: [
                {
                  sheetId: 0,
                  startColumnIndex: 2,
                  endColumnIndex: 3,
                }
              ],
              booleanRule: {
                condition: {
                  type: "TEXT_CONTAINS",
                  values: [{ userEnteredValue: "the" }]
                },
                format: {
                  textFormat: {
                    bold: true,
                    foregroundColor: { blue: 1 }
                  },
                  backgroundColor: { red: 1, green: 1 }
                }
              }
            },
            index: 0
          }
        }
      ]
    },
  })

  console.log(res.data)
}

async function getNewToken(client) {
  const authUrl = client.generateAuthUrl({
    access_type: 'offline',
    scope: 'https://www.googleapis.com/auth/spreadsheets',
  })
  console.log('Authorize this app by visiting this url:', authUrl)
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  })

  const code = await rl.question('Enter the code from that page here: ')
  rl.close()
  try {
    const { tokens } = await client.getToken(code)

    // Store the token to disk for later program executions.
    await fs.writeFile('token.json', JSON.stringify(tokens))
    console.log("Token stored to `token.json`")
    return tokens
  } catch (error) {
    console.error('\nError while trying to retrieve access token\n', error)
    throw error
  }
}

const spreadsheetId = '_________________________________________'

runSample(spreadsheetId)

Добавлю, что создание правил условного форматирования через api мало отличается от их создания непосредственно в таблице: каждое новое правило добавляется к предыдущим, а не заменяет его, если условие выполняется сразу в нескольких правилах, то срабатывает только самое первое. Порядок правил определяет значение поля index и изменить его можно запросом updateConditionalFormatRule, ну и конечно же правило можно удалить запросом deleteConditionalFormatRule. Правила не фиксируют формат при создании, а отслеживают изменение данных (и соответственно условия правила).

Если такое поведение вас в корне не устраивает, то можно пойти другим путем: первым запросом прочитать данные из таблицы, программно проанализировать их и сформировать данные для updateCells запроса.

Примерно так будет выглядеть запрос на выборочное форматирование ячеек:

  res = await api.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: {
      requests: [
        {
          updateCells: {
            range: {
              sheetId: 0,
              startColumnIndex: 2,
              endColumnIndex: 3,
              startRowIndex: 10,
              endRowIndex: 14,
            },
            rows: [
              { values: { userEnteredFormat: { backgroundColor: { red: 1, green: 1 } } } },
              { values: null },
              { values: null },
              { values: { userEnteredFormat: { backgroundColor: { red: 1, green: 1 } } } },
            ],
            fields: "userEnteredFormat.backgroundColor",
          }
        }
      ]
    },
  })

Здесь в диапазоне из четырех строк формат применяется только к первой и последней строке, а в строках со значением null цвет фона будет сброшен в исходное состояние.