here is the code:
export async function updateCellsToLeftOfValues({
sheetId,
tabName,
searchValues,
newValues
}: {
sheetId: string;
tabName: string;
searchValues: string[];
newValues: string[];
}): Promise<{ result: string }> {
// Ensure the searchValues and newValues arrays are of the same length
if (searchValues.length !== newValues.length) {
throw new Error("The length of searchValues and newValues must be the same.");
}
// Construct the range to search the entire sheet
const range = `${tabName}!A:Z`;
// Fetch the values from the specified range
const getUrl = `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${range}`;
const getResponse = await fetchWithZapier(getUrl);
await getResponse.throwErrorIfNotOk();
const data = await getResponse.json();
// Prepare the updates
const updates = [];
// Find the cells containing the search values
for (let i = 0; i < searchValues.length; i++) {
const searchValue = searchValues[i];
let found = false;
for (let rowIndex = 0; rowIndex < data.values.length; rowIndex++) {
for (let colIndex = 0; colIndex < data.values[rowIndex].length; colIndex++) {
if (data.values[rowIndex][colIndex] === searchValue) {
// Ensure we are not in the first column
if (colIndex > 0) {
const updateRange = `${tabName}!${String.fromCharCode(65 + colIndex - 1)}${rowIndex + 1}`;
updates.push({
range: updateRange,
values: [[newValues[i]]]
});
found = true;
break;
}
}
}
if (found) break;
}
if (!found) {
throw new Error(`Value "${searchValue}" not found in the sheet.`);
}
}
// Perform the updates
for (const update of updates) {
const updateUrl = `https://sheets.googleapis.com/v4/spreadsheets/${sheetId}/values/${update.range}?valueInputOption=RAW`;
const updateResponse = await fetchWithZapier(updateUrl, {
method: 'PUT',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
range: update.range,
majorDimension: "ROWS",
values: update.values
})
});
await updateResponse.throwErrorIfNotOk();
}
return { result: `Updated ${updates.length} cells with new values.` };
}