Skip to main content
Question

inputs fields are missing in custom action

  • 22 July 2024
  • 1 reply
  • 28 views

Hello,

Here's a summary of my action requirements:

  • Sheet Details: A specific Google Sheet and tab.
  • Search Values: A list of values to search for in the entire sheet.
  • Update Values: A list of values to update the cells to the left of each found value, in the same order as the search values.

but the problem is that search values and update values are missing and not working.

 

Here is a photo of the problem and the code generated: 

 

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

1 reply

M
Userlevel 1

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.` };

}

Terms & Conditions