Skip to content

AI Agents & MCPs & AI Workflow Automation • (~400 MCP servers for AI agents) • AI Automation / AI Agent with MCPs • AI Workflows & AI Agents • MCPs for AI Agents

4.7 KB
Raw Blame
import { createAction, Property } from '@activepieces/pieces-framework';
import { sheets as googleSheets } from '@googleapis/sheets';
import { areSheetIdsValid, createGoogleClient, googleSheetsAuth } from '../common/common';
import { commonProps } from '../common/props';
import { deleteMultipleRowsActionOutputSchema } from '../output-schemas';

export const deleteMultipleRowsAction = createAction({
	auth: googleSheetsAuth,
	name: 'delete-multiple-rows',
	classification: 'DESTRUCTIVE',
	displayName: 'Delete Multiple Rows',
	description:
		'Deletes a contiguous range of rows, or a list of specific row numbers. Row numbers are 1-based.',
	audience: 'human',
	aiMetadata: {
		description:
			'Permanently deletes rows from a worksheet in one batch, in either of two modes: a contiguous range given by starting and ending row, or a comma-separated list of specific 1-based row numbers. Use instead of Delete Row when several rows must go at once, and Clear Row(s) instead when the rows should stay in place with only their contents erased. Not idempotent — surviving rows renumber after each deletion, so repeating the same call removes different rows; re-resolve the targets before any retry.',
		idempotent: false,
	},
	propertyGroups: [
		{
			key: 'spreadsheet',
			display: 'section',
			label: 'Spreadsheet',
			icon: 'file',
			props: ['spreadsheetId', 'sheetId'],
		},
		{
			key: 'target_rows',
			display: 'section',
			label: 'Rows',
			icon: 'markdown',
			props: ['mode', 'startingRow', 'endingRow', 'rowNumbers'],
		},
	],
	props: {
		...commonProps,
		mode: Property.StaticDropdown({
			displayName: 'Mode',
			description:
				'Delete a range of rows, or a list of specific row numbers.',
			required: true,
			defaultValue: 'range',
			options: {
				disabled: false,
				options: [
					{ label: 'Contiguous Range', value: 'range' },
					{ label: 'Specific Row Numbers', value: 'list' },
				],
			},
		}),
		startingRow: Property.Number({
			displayName: 'Starting Row',
			description: 'First row to delete (used in "Contiguous Range" mode).',
			required: false,
		}),
		endingRow: Property.Number({
			displayName: 'Ending Row',
			description: 'Last row to delete (used in "Contiguous Range" mode).',
			required: false,
		}),
		rowNumbers: Property.ShortText({
			displayName: 'Row Numbers',
			description:
				'Comma-separated row numbers to delete.',
			required: false,
			placeholder: '3, 5, 8',
		}),
	},
	outputSchema: deleteMultipleRowsActionOutputSchema,
	async run({ auth, propsValue }) {
		const { spreadsheetId, sheetId, mode, startingRow, endingRow, rowNumbers } = propsValue;

		if (!areSheetIdsValid(spreadsheetId, sheetId)) {
			throw new Error('Please select a spreadsheet and sheet first.');
		}

		const numericSheetId = sheetId as number;
		const authClient = await createGoogleClient(auth);
		const sheets = googleSheets({ version: 'v4', auth: authClient });

		const deletionRanges = buildDeletionRanges({ mode, startingRow, endingRow, rowNumbers });

		if (deletionRanges.length === 0) {
			throw new Error('No valid rows were provided to delete.');
		}

		const requests = deletionRanges.map(({ start, end }) => ({
			deleteDimension: {
				range: {
					sheetId: numericSheetId,
					dimension: 'ROWS',
					startIndex: start,
					endIndex: end,
				},
			},
		}));

		const response = await sheets.spreadsheets.batchUpdate({
			spreadsheetId: spreadsheetId as string,
			requestBody: {
				requests,
			},
		});

		return {
			success: true,
			deletedRanges: deletionRanges.map(({ start, end }) => ({
				startRow: start + 1,
				endRow: end,
			})),
			replies: response.data.replies,
		};
	},
});

type BuildDeletionRangesParams = {
	mode: string;
	startingRow: number | undefined;
	endingRow: number | undefined;
	rowNumbers: string | undefined;
};

function buildDeletionRanges({
	mode,
	startingRow,
	endingRow,
	rowNumbers,
}: BuildDeletionRangesParams): { start: number; end: number }[] {
	if (mode === 'range') {
		if (!startingRow || startingRow < 1) {
			throw new Error('Starting row must be 1 or greater.');
		}
		const lastRow = endingRow && endingRow >= startingRow ? endingRow : startingRow;
		return [{ start: startingRow - 1, end: lastRow }];
	}

	if (!rowNumbers || rowNumbers.trim().length === 0) {
		throw new Error('Please provide a comma-separated list of row numbers.');
	}

	const parsed = rowNumbers
		.split(',')
		.map((token) => token.trim())
		.filter((token) => token.length > 0)
		.map((token) => parseInt(token, 10))
		.filter((n) => Number.isInteger(n) && n >= 1);

	if (parsed.length === 0) {
		throw new Error('No valid row numbers were parsed from the input.');
	}

	const sortedDesc = Array.from(new Set(parsed)).sort((a, b) => b - a);
	return sortedDesc.map((rowNumber) => ({ start: rowNumber - 1, end: rowNumber }));
}