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

16.0 KB
Raw Blame
import { googleSheetsAuth } from '../common/common';
import {
	createAction,
	DropdownOption,
	DynamicPropsValue,
	Property,
} from '@activepieces/pieces-framework';
import {
	Dimension,
	googleSheetsCommon,
	objectToArray,
	ValueInputOption,
	columnToLabel,
	areSheetIdsValid,
	GoogleSheetsAuthValue,
	createGoogleClient,
} from '../common/common';
import { getWorkSheetName, getWorkSheetGridSize } from '../triggers/helpers';
import { sheets as googleSheets, sheets_v4 } from '@googleapis/sheets';
import { MarkdownVariant } from '@activepieces/pieces-framework';
import { parse } from 'csv-parse/sync';
import { commonProps } from '../common/props';
import { googleSheetsInsertMultipleRowsActionOutputSchema } from '../output-schemas';

type RowValueType = Record<string, any>;

export const insertMultipleRowsAction = createAction({
	auth: googleSheetsAuth,
	name: 'google-sheets-insert-multiple-rows',
	classification: 'WRITE',
	displayName: 'Add Multiple Rows',
	description: 'Add multiple rows of data at once to a specific spreadsheet.',
	audience: 'human',
	aiMetadata: {
		description:
			'Bulk-appends many rows to a worksheet in one call, accepting input as CSV, JSON, or per-column values. Use for batch inserts instead of calling Add Row repeatedly. Optional flags can overwrite existing data or skip duplicates keyed on a chosen column; in default append mode it is not idempotent — repeating it adds the rows again.',
		idempotent: false,
	},
	propertyGroups: [
		{
			key: 'spreadsheet',
			display: 'section',
			label: 'Spreadsheet',
			icon: 'file',
			props: ['spreadsheetId', 'sheetId'],
		},
		{
			key: 'new_rows',
			display: 'section',
			label: 'New Rows',
			icon: 'text',
			props: ['input_type', 'headerRow', 'values'],
		},
		{
			key: 'options',
			display: 'section',
			label: 'Options',
			icon: 'sliders',
			props: ['overwrite', 'check_for_duplicate', 'check_for_duplicate_column'],
		},
	],
	props: {
		...commonProps,
		input_type: Property.StaticDropdown({
			displayName: 'Rows Data Format',
			description: 'Select the format of the input values to be added into the worksheet.',
			required: true,
			defaultValue: 'column_names',
			display: 'cards',
			options: {
				disabled: false,
				options: [
					{
						value: 'csv',
						label: 'CSV',
						description: 'Line per row',
						icon: 'file',
					},
					{
						value: 'json',
						label: 'JSON',
						description: 'List of objects',
						icon: 'code',
					},
					{
						value: 'column_names',
						label: 'Column Names',
						description: 'Field per column',
						icon: 'type',
					},
				],
			},
		}),
		values: Property.DynamicProperties({
			auth: googleSheetsAuth,
			displayName: 'Values',
			description: 'The values to add.',
			required: true,
			refreshers: ['sheetId', 'spreadsheetId', 'input_type', 'headerRow'],
			props: async ({ auth, sheetId, spreadsheetId, input_type, headerRow }) => {
				const sheet_id = Number(sheetId);
				const spreadsheet_id = spreadsheetId as unknown as string;
				const valuesInputType = input_type as unknown as string;

				if (
					!auth ||
					(spreadsheet_id ?? '').toString().length === 0 ||
					(sheet_id ?? '').toString().length === 0 ||
					!['csv', 'json', 'column_names'].includes(valuesInputType)
				) {
					return {};
				}

				const fields: DynamicPropsValue = {};

				switch (valuesInputType) {
					case 'csv':
						fields['markdown'] = Property.MarkDown({
							value: `Ensure the first row contains column headers that match the sheet's column names.`,
							variant: MarkdownVariant.INFO,
						});
						fields['values'] = Property.LongText({
							displayName: 'CSV',
							required: true,
						});
						break;
					case 'json':
						fields['markdown'] = Property.MarkDown({
							value: `Provide values in JSON format. Ensure the column names match the sheet's header.`,
							variant: MarkdownVariant.INFO,
						});
						fields['values'] = Property.Json({
							displayName: 'JSON',
							required: true,
							defaultValue: [
								{
									column1: 'value1',
									column2: 'value2',
								},
								{
									column1: 'value3',
									column2: 'value4',
								},
							],
						});
						break;
					case 'column_names': {
						const headers = await googleSheetsCommon.getGoogleSheetRows({
							spreadsheetId: spreadsheet_id,
							auth: auth,
							sheetId: sheet_id,
							rowIndex_s: 1,
							rowIndex_e: 1,
							headerRow: (headerRow as unknown as number) || 1,
						});
						const firstRow = headers[0].values ?? {};

						//check for empty headers
						if (Object.keys(firstRow).length === 0) {
							fields['markdown'] = Property.MarkDown({
								value: `No headers found in this worksheet. Add a header row, then reselect the worksheet to load its columns.`,
								variant: MarkdownVariant.INFO,
							});
						} else {
							const columns: {
								[key: string]: any;
							} = {};

							for (const key in firstRow) {
								columns[key] = Property.ShortText({
									displayName: firstRow[key],
									// description: firstRow[key],
									required: false,
									defaultValue: '',
								});
							}
							fields['values'] = Property.Array({
								displayName: 'Values',
								required: false,
								properties: columns,
							});
						}
					}
				}
				return fields;
			},
		}),
		overwrite: Property.Checkbox({
			displayName: 'Overwrite Existing Data',
			description:
				'Replace every existing row with the new data. Extra rows are cleared.',
			required: false,
			defaultValue: false,
		}),
		check_for_duplicate: Property.Checkbox({
			displayName: 'Skip Duplicate Rows',
			description:
				'Skip rows whose value in the chosen column already exists.',
			required: false,
			defaultValue: false,
		}),
		check_for_duplicate_column: Property.DynamicProperties({
			auth: googleSheetsAuth,
			displayName: 'Duplicate Value Column',
			description: 'The column to check for duplicate values.',
			refreshers: ['spreadsheetId', 'sheetId', 'check_for_duplicate', 'headerRow'],
			required: false,
			props: async ({ auth, spreadsheetId, sheetId, check_for_duplicate, headerRow }) => {
				const sheet_id = Number(sheetId);
				const spreadsheet_id = spreadsheetId as unknown as string;
				const checkForExisting = check_for_duplicate as unknown as boolean;
				if (
					!auth ||
					(spreadsheet_id ?? '').toString().length === 0 ||
					(sheet_id ?? '').toString().length === 0
				) {
					return {};
				}

				const fields: DynamicPropsValue = {};

				if (checkForExisting) {
					const headers = await googleSheetsCommon.getGoogleSheetRows({
						spreadsheetId: spreadsheet_id,
						auth: auth as GoogleSheetsAuthValue,
						sheetId: sheet_id,
						rowIndex_s: 1,
						rowIndex_e: 1,
						headerRow: (headerRow as unknown as number) || 1,
					});
					const firstRow = headers[0].values ?? {};

					//check for empty headers
					if (Object.keys(firstRow).length === 0) {
						fields['markdown'] = Property.MarkDown({
							value: `No headers found in this worksheet. Add a header row, then reselect the worksheet to load its columns.`,
							variant: MarkdownVariant.INFO,
						});
					} else {
						const headers: DropdownOption<string>[] = [];
						for (const key in firstRow) {
							headers.push({
								label: firstRow[key].toString(),
								value: key.toString(),
							});
						}

						fields['column_name'] = Property.StaticDropdown({
							displayName: 'Column to Check for Duplicates',
							description:
								'Rows with a repeated value in this column are skipped.',
							required: true,
							options: {
								disabled: false,
								options: headers,
							},
						});
					}
				}

				return fields;
			},
		}),
		as_string: Property.Checkbox({
			displayName: 'As String',
			description:
				'Store dates and formulas as plain text instead of evaluating them.',
			required: false,
			advanced: true,
		}),
		headerRow: Property.Number({
			displayName: 'Header Row',
			description: 'The row that contains the column names.',
			required: true,
			defaultValue: 1,
		}),
	},
	outputSchema: googleSheetsInsertMultipleRowsActionOutputSchema,

	async run(context) {
		const {
			spreadsheetId: inputSpreadsheetId,
			sheetId: inputSheetId,
			input_type: valuesInputType,
			overwrite: overwriteValues,
			check_for_duplicate: checkForDuplicateValues,
			values: { values: rowValuesInput },
			as_string: asString,
			headerRow,
		} = context.propsValue;

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

		const sheetId = Number(inputSheetId);
		const spreadsheetId = inputSpreadsheetId as string;

		const duplicateColumn = context.propsValue.check_for_duplicate_column?.['column_name'];
		const sheetName = await getWorkSheetName(context.auth, spreadsheetId, sheetId);

		const rowHeaders = await googleSheetsCommon.getGoogleSheetRows({
			spreadsheetId: spreadsheetId,
			auth: context.auth,
			sheetId: sheetId,
			rowIndex_s: 1,
			rowIndex_e: 1,
			headerRow: headerRow,
		});

		const sheetHeaders = rowHeaders[0]?.values ?? {};

		const authClient = await createGoogleClient(context.auth);
		const sheets = googleSheets({ version: 'v4', auth: authClient });

		const formattedValues = await formatInputRows(
			sheets,
			spreadsheetId,
			sheetName,
			valuesInputType,
			rowValuesInput,
			sheetHeaders,
		);

		const valueInputOption = asString ? ValueInputOption.RAW : ValueInputOption.USER_ENTERED;

		if (overwriteValues) {
			const sheetGridRange = await getWorkSheetGridSize(context.auth, spreadsheetId, sheetId);
			const existingGridRowCount = sheetGridRange.rowCount ?? 0;
			return handleOverwrite(
				sheets,
				spreadsheetId,
				sheetName,
				formattedValues,
				existingGridRowCount,
				valueInputOption,
			);
		}

		if (checkForDuplicateValues) {
			const existingSheetValues = await googleSheetsCommon.getGoogleSheetRows({
				spreadsheetId: spreadsheetId,
				auth: context.auth,
				sheetId: sheetId,
				rowIndex_s: 1,
				rowIndex_e: undefined,
				headerRow: headerRow,
			});
			return handleDuplicates(
				sheets,
				spreadsheetId,
				sheetName,
				formattedValues,
				existingSheetValues,
				duplicateColumn,
				valueInputOption,
			);
		}

		return normalInsert(sheets, spreadsheetId, sheetName, formattedValues, valueInputOption);
	},
});

async function handleOverwrite(
	sheets: sheets_v4.Sheets,
	spreadSheetId: string,
	sheetName: string,
	formattedValues: any[],
	existingGridRowCount: number,
	valueInputOption: ValueInputOption,
) {
	const existingRowCount = existingGridRowCount;
	const inputRowCount = formattedValues.length;

	const updateResponse = await sheets.spreadsheets.values.batchUpdate({
		spreadsheetId: spreadSheetId,
		requestBody: {
			data: [
				{
					range: `${sheetName}!A2:ZZZ${inputRowCount + 1}`,
					majorDimension: Dimension.ROWS,
					values: formattedValues.map((row) => objectToArray(row)),
				},
			],
			valueInputOption,
		},
	});

	// Determine if clearing rows is necessary and within grid size
	const clearStartRow = inputRowCount + 2; // Start clearing after the last input row
	const clearEndRow = Math.max(clearStartRow, existingRowCount);

	if (clearStartRow <= existingGridRowCount) {
		const boundedClearEndRow = Math.min(clearEndRow, existingGridRowCount);
		const clearRowsResponse = await sheets.spreadsheets.values.batchClear({
			spreadsheetId: spreadSheetId,
			requestBody: {
				ranges: [`${sheetName}!A${clearStartRow}:ZZZ${boundedClearEndRow}`],
			},
		});

		return {
			...updateResponse.data,
			...clearRowsResponse.data,
		};
	}
	return updateResponse.data;
}

async function handleDuplicates(
	sheets: sheets_v4.Sheets,
	spreadSheetId: string,
	sheetName: string,
	formattedInputRows: any[],
	existingSheetValues: any[],
	duplicateColumn: string,
	valueInputOption: ValueInputOption,
) {
	const uniqueValues = formattedInputRows.filter(
		(inputRow) =>
			!existingSheetValues.some((existingRow) => {
				const existingValue = existingRow?.values?.[duplicateColumn];
				const inputValue = inputRow?.[duplicateColumn];
				return (
					existingValue != null &&
					inputValue != null &&
					String(existingValue).toLowerCase().trim() === String(inputValue).toLowerCase().trim()
				);
			}),
	);

	const response = await sheets.spreadsheets.values.append({
		range: sheetName + '!A:A',
		spreadsheetId: spreadSheetId,
		valueInputOption,
		requestBody: {
			values: uniqueValues.map((row) => objectToArray(row)),
			majorDimension: Dimension.ROWS,
		},
	});

	return response.data;
}

async function normalInsert(
	sheets: sheets_v4.Sheets,
	spreadSheetId: string,
	sheetName: string,
	formattedValues: any[],
	valueInputOption: ValueInputOption,
) {
	const response = await sheets.spreadsheets.values.append({
		range: sheetName + '!A:A',
		spreadsheetId: spreadSheetId,
		valueInputOption,
		requestBody: {
			values: formattedValues.map((row) => objectToArray(row)),
			majorDimension: Dimension.ROWS,
		},
	});
	return response.data;
}

async function formatInputRows(
	sheets: sheets_v4.Sheets,
	spreadSheetId: string,
	sheetName: string,
	valuesInputType: string,
	rowValuesInput: any,
	sheetHeaders: RowValueType,
): Promise<RowValueType[]> {
	let formattedInputRows: any[] = [];

	switch (valuesInputType) {
		case 'csv':
			formattedInputRows = convertCsvToRawValues(rowValuesInput as string, ',', sheetHeaders);
			break;
		case 'json':
			formattedInputRows = await convertJsonToRawValues(
				sheets,
				spreadSheetId,
				sheetName,
				rowValuesInput as string,
				sheetHeaders,
			);
			break;
		case 'column_names':
			formattedInputRows = rowValuesInput as RowValueType[];
			break;
	}

	return formattedInputRows;
}

async function convertJsonToRawValues(
	sheets: sheets_v4.Sheets,
	spreadSheetId: string,
	sheetName: string,
	json: string | Record<string, any>[],
	labelHeaders: RowValueType,
): Promise<RowValueType[]> {
	let data: RowValueType[];

	// If the input is a JSON string
	if (typeof json === 'string') {
		try {
			data = JSON.parse(json);
		} catch (error) {
			throw new Error('Invalid JSON format for row values');
		}
	} else {
		// If the input is already an array of objects, use it directly
		data = json;
	}

	// Ensure the input is an array of objects
	if (!Array.isArray(data) || typeof data[0] !== 'object') {
		throw new Error('Input must be an array of objects or a valid JSON string representing it.');
	}

	// Collect all possible headers from the data
	const allHeaders = new Set<string>();
	data.forEach((row) => {
		Object.keys(row).forEach((key) => allHeaders.add(key));
	});

	// Identify headers not present in labelHeaders
	const additionalHeaders = Array.from(allHeaders).filter(
		(header) => !Object.values(labelHeaders).includes(header),
	);

	//add missing headers to labelHeaders
	additionalHeaders.forEach((header) => {
		labelHeaders[columnToLabel(Object.keys(labelHeaders).length)] = header;
	});

	// update sheets with new headers
	if (additionalHeaders.length > 0) {
		await sheets.spreadsheets.values.update({
			range: `${sheetName}!A1:ZZZ1`,
			spreadsheetId: spreadSheetId,
			valueInputOption: ValueInputOption.USER_ENTERED,
			requestBody: {
				majorDimension: Dimension.ROWS,
				values: [objectToArray(labelHeaders)],
			},
		});
	}

	return data.map((row: RowValueType) => {
		return Object.entries(labelHeaders).reduce((acc, [labelColumn, csvHeader]) => {
			acc[labelColumn] = row[csvHeader] ?? '';
			return acc;
		}, {} as RowValueType);
	});
}

function convertCsvToRawValues(csvText: string, delimiter: string, labelHeaders: RowValueType) {
	// Split CSV into rows
	const rows: Record<string, any>[] = parse(csvText, {
		delimiter: delimiter,
		columns: true,
	});

	const result = rows.map((row) => {
		// Normalize record keys to lowercase
		const normalizedRow = Object.keys(row).reduce((acc, key) => {
			acc[key.toLowerCase().trim()] = row[key];
			return acc;
		}, {} as Record<string, any>);

		const transformedRow: Record<string, any> = {};
		for (const key in labelHeaders) {
			// Match labels to normalized keys
			const normalizedKey = labelHeaders[key].toLowerCase();
			transformedRow[key] = normalizedRow[normalizedKey] || '';
		}
		return transformedRow;
	});
	return result;
}