[ACTION] Google Sheets - conditional format rules / data validation #18931
Description
Is there a specific app this action is for?
Yes, this is for the Google Sheets app component: https://github.com/PipedreamHQ/pipedream/tree/master/components/google_sheets
Please describe the missing actions and link to the relevant API docs.
Add 6 new actions to support advanced cell formatting, data validation, and protection:
-
Set Data Validation - Add dropdowns, checkboxes, date/number validation to cells
-
Add Conditional Format Rule - Create conditional formatting with color scales or custom formulas
-
Update Conditional Format Rule - Modify existing conditional formatting rule
-
Delete Conditional Format Rule - Remove conditional formatting rule by index
-
Merge Cells - Merge a range of cells into a single cell
-
Add Protected Range - Add edit protection to cell range with permissions
Implementation Template (based on existing delete-rows action):
import googleSheets from "../../google_sheets.app.mjs";
export default {
key: "google_sheets-{action-name}",
name: "{Action Name}",
description: "{Description}. [See the documentation]({API_LINK})",
version: "0.0.1",
type: "action",
annotations: {
destructiveHint: false, // true for delete operations
openWorldHint: true,
readOnlyHint: false,
},
props: {
googleSheets,
drive: {
propDefinition: [
googleSheets,
"watchedDrive",
],
},
sheetId: {
propDefinition: [
googleSheets,
"sheetID",
(c) => ({
driveId: googleSheets.methods.getDriveId(c.drive),
}),
],
},
worksheetId: {
propDefinition: [
googleSheets,
"worksheetIDs",
(c) => ({
sheetId: c.sheetId,
}),
],
},
// Action-specific props here
},
async run() {
const request = {
spreadsheetId: this.sheetId,
requestBody: {
requests: [
{
// Insert specific request type here
// e.g., setDataValidation, addConditionalFormatRule, etc.
},
],
},
};
return await this.googleSheets.batchUpdate(request);
},
};Example: Set Data Validation Action
import googleSheets from "../../google_sheets.app.mjs";
export default {
key: "google_sheets-set-data-validation",
name: "Set Data Validation",
description: "Add data validation rules to cells (dropdowns, checkboxes, date/number validation). [See the documentation](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#SetDataValidationRequest)",
version: "0.0.1",
type: "action",
annotations: {
destructiveHint: false,
openWorldHint: true,
readOnlyHint: false,
},
props: {
googleSheets,
drive: {
propDefinition: [
googleSheets,
"watchedDrive",
],
},
sheetId: {
propDefinition: [
googleSheets,
"sheetID",
(c) => ({
driveId: googleSheets.methods.getDriveId(c.drive),
}),
],
},
worksheetId: {
propDefinition: [
googleSheets,
"worksheetIDs",
(c) => ({
sheetId: c.sheetId,
}),
],
},
range: {
propDefinition: [
googleSheets,
"range",
],
description: "The range of cells to apply validation (e.g., `A1:A10`)",
},
validationType: {
type: "string",
label: "Validation Type",
description: "The type of data validation",
options: [
"ONE_OF_LIST",
"NUMBER_GREATER",
"NUMBER_LESS",
"DATE_BEFORE",
"DATE_AFTER",
"TEXT_CONTAINS",
"TEXT_IS_EMAIL",
"TEXT_IS_URL",
"BOOLEAN",
// ... more types
],
},
validationValues: {
type: "string[]",
label: "Validation Values",
description: "Values for validation (e.g., dropdown options)",
optional: true,
},
},
async run() {
const {
startCol,
endCol,
startRow,
endRow,
} = this.googleSheets._parseRangeString(`${this.worksheetId}!${this.range}`);
const request = {
spreadsheetId: this.sheetId,
requestBody: {
requests: [
{
setDataValidation: {
range: {
sheetId: this.worksheetId,
startRowIndex: startRow,
endRowIndex: endRow,
startColumnIndex: startCol.charCodeAt(0) - 65,
endColumnIndex: endCol.charCodeAt(0) - 64,
},
rule: {
condition: {
type: this.validationType,
values: this.validationValues?.map(v => ({ userEnteredValue: v })),
},
showCustomUi: true,
strict: true,
},
},
},
],
},
};
return await this.googleSheets.batchUpdate(request);
},
};All other actions follow the same pattern with their respective request types (addConditionalFormatRule, mergeCells, etc.).