How to Write a Node.js Code to Search Through an Array and Find a Pre-Defined Value in a Google Sheet Trigger Event?

Help
1

This topic was automatically generated from Slack. You can find the original thread here.

I am using the a trigger to look to see what changes were made to a particular GOOGLE SHEET.

The variable that is returned is called:
steps.trigger.event.changes

And it appears to have several arrays inside of it:

changes.0
changes.1
changes.2

And then inside of each of those are:

changes.0.cell
changes.0.new_value
changes.0.previous_value

Can you write me a node.js code to search through this array and find a pre-defined value?

I’d like to have two input fields:
WORD TO SEARCH FOR:
ARRAY TO SEARCH THRU:

2

To search through the steps.trigger.event.changes array for a predefined value in a Google Sheets workflow, you can use the following Node.js code in a Pipedream component. This code will take two inputs: the word to search for and the array to search through. export default defineComponent({ props: { wordToSearchFor: { type: "string", label: "Word to Search For", }, arrayToSearchThru: { type: "string[]", label: "Array to Search Through", }, }, async run({ steps, $ }) { const changes = steps.trigger.event.changes; const wordToSearchFor = this.wordToSearchFor; const arrayToSearchThru = this.arrayToSearchThru; // Filter the changes array to find entries where the word is present const results = changes.filter(change => { return arrayToSearchThru.some(field => { return change[field] && change[field].includes(wordToSearchFor); }); }); return results; }, }); ### Explanation: - Props: The component has two input fields: wordToSearchFor and arrayToSearchThru. The arrayToSearchThru is expected to be an array of strings, each representing a field in the changes object (e.g., cell, new_value, previous_value). - Filter Logic: The code filters the changes array to find objects where any of the specified fields contain the wordToSearchFor. - Return: The filtered results are returned, containing only the changes where the word was found in the specified fields. You can use this component in your Pipedream workflow to dynamically search through the changes in your Google Sheet based on the inputs you provide.

3

The code appears to work except it does not return a value for me to use in my workflow.

What I would need is the array stored as three values.

  1. cell
  2. new_value
  3. previous_value
4

To help you return the array as three separate values (cell, new_value, and previous_value) in your Pipedream workflow, you can use the $.export function to export these values. Here’s an example of how you can modify your code to achieve this: export default defineComponent({ async run({ steps, $ }) { // Assuming you have an array with the values const data = ["A1", "newValue", "oldValue"]; // Destructure the array into separate variables const [cell, new_value, previous_value] = data; // Export each value for use in the workflow $.export("cell", cell); $.export("new_value", new_value); $.export("previous_value", previous_value); // Optionally, return the values as an object return { cell, new_value, previous_value }; } }); ### Explanation: 1. Destructuring the Array: The array data is destructured into three variables: cell, new_value, and previous_value. 2. Exporting Values: Each value is exported using $.export, which makes them available as step exports in your workflow. 3. Returning Values: Optionally, you can return the values as an object at the end of the run method. This approach will allow you to access cell, new_value, and previous_value in subsequent steps of your Pipedream workflow.

5

This is what I came up with, but it’s not returning the values.

export default defineComponent({
  props: {
    wordToSearchFor: {
      type: "string",
      label: "Word to Search For",
    },
    arrayToSearchThru: {
      type: "string[]",
      label: "Array to Search Through",
    },
  },
  async run({ steps, $ }) {
    const changes = steps.trigger.event.changes; // The array of changes
    const wordToSearchFor = this.wordToSearchFor; // Word to search for
    const arrayToSearchThru = this.arrayToSearchThru; // Fields to search through

    // Filter changes to find matches
    const results = changes
      .filter(change => {
        // Check if the word exists in any of the specified fields
        return arrayToSearchThru.some(field => {
          return change[field] && change[field].includes(wordToSearchFor);
        });
      })
      .map(change => {
        // Map the results to return desired values
        const { cell, new_value, previous_value } = change;
        return { cell, new_value, previous_value };
      });

    return results; // Return the filtered and mapped results
  },
});
6

It looks like you’re trying to filter an array of changes to find matches based on a specific word and fields. Let’s correct the syntax and ensure the logic is implemented correctly. Here’s the updated component code: export default defineComponent({ props: { wordToSearchFor: { type: "string", label: "Word to Search For", }, arrayToSearchThru: { type: "string[]", label: "Array to Search Through", }, }, async run({ steps, $ }) { const changes = steps.trigger.event.changes; // The array of changes const wordToSearchFor = this.wordToSearchFor; // Word to search for const arrayToSearchThru = this.arrayToSearchThru; // Fields to search through // Filter changes to find matches const results = changes .filter(change => { // Check if the word exists in any of the specified fields return arrayToSearchThru.some(field => { return change[field] && change[field].includes(wordToSearchFor); }); }) .map(change => { // Map the results to return desired values const { cell, new_value, previous_value } = change; return { cell, new_value, previous_value }; }); return results; // Return the filtered and mapped results }, }); ### Key Corrections: - Replaced HTML entities like > and & with their JavaScript equivalents: > and &&. - Ensured that the logic for filtering and mapping is correctly implemented. This code should now correctly filter the changes array based on the specified word and fields, and return the desired values. If you continue to experience issues, please ensure that the changes array and the fields you’re searching through contain the expected data.

7

This did not work. Let me show you a full example of the steps.trigger.event.changes.

[{“cell”:“X:1”,“new_value”:“INVESTED FANS”,“previous_value”:“”},{“cell”:“Y:1”,“new_value”:“LOW”,“previous_value”:“”},{“cell”:“Z:1”,“new_value”:“MID-HIGH”,“previous_value”:“”},{“cell”:“AA:1”,“new_value”:“STATUS”,“previous_value”:“”},{“cell”:“AA:57”,“new_value”:“SEND”,“previous_value”:“”},{“cell”:“E:127”,“new_value”:“B”,“previous_value”:“”}]

No values are being returned using the following code:

export default defineComponent({
  props: {
    wordToSearchFor: {
      type: "string",
      label: "Word to Search For",
    },
    arrayToSearchThru: {
      type: "string[]",
      label: "Array to Search Through",
    },
  },
  async run({ steps, $ }) {
    const changes = steps.trigger.event.changes; // The array of changes
    const wordToSearchFor = this.wordToSearchFor; // Word to search for
    const arrayToSearchThru = this.arrayToSearchThru; // Fields to search through

    // Filter changes to find matches
    const results = changes
      .filter(change => {
        // Check if the word exists in any of the specified fields
        return arrayToSearchThru.some(field => {
          return change[field] && change[field].includes(wordToSearchFor);
        });
      })
      .map(change => {
        // Map the results to return desired values
        const { cell, new_value, previous_value } = change;
        return { cell, new_value, previous_value };
      });

    return results; // Return the filtered and mapped results
  },
});
8

It seems like there might be a misunderstanding in how you’re accessing the fields within the changes array. The changes array contains objects with keys like cell, new_value, and previous_value, but your code is attempting to access fields dynamically using arrayToSearchThru. To fix this, you should directly check the new_value field for the presence of wordToSearchFor. Here’s an updated version of your code: export default defineComponent({ props: { wordToSearchFor: { type: "string", label: "Word to Search For", }, arrayToSearchThru: { type: "string[]", label: "Array to Search Through", }, }, async run({ steps, $ }) { const changes = steps.trigger.event.changes; // The array of changes const wordToSearchFor = this.wordToSearchFor; // Word to search for // Filter changes to find matches in the new_value field const results = changes .filter(change => { // Check if the word exists in the new_value return change.new_value && change.new_value.includes(wordToSearchFor); }) .map(change => { // Map the results to return desired values const { cell, new_value, previous_value } = change; return { cell, new_value, previous_value }; }); return results; // Return the filtered and mapped results }, }); In this version, the code filters the changes array by checking if wordToSearchFor is included in the new_value of each change. This should return the expected results if wordToSearchFor is present in any new_value. If you need to search through other fields, you can adjust the logic accordingly.

9

We are close but not quite. Using this array as an example:

[{“cell”:“X:1”,“new_value”:“INVESTED FANS”,“previous_value”:“”},{“cell”:“Y:1”,“new_value”:“LOW”,“previous_value”:“”},{“cell”:“Z:1”,“new_value”:“MID-HIGH”,“previous_value”:“”},{“cell”:“AA:1”,“new_value”:“STATUS”,“previous_value”:“”},{“cell”:“AA:57”,“new_value”:“SEND”,“previous_value”:“”},{“cell”:“E:127”,“new_value”:“B”,“previous_value”:“”}]

If we were searching for the value “SEND”, this would be the answer.
{“cell”:“AA:57”,“new_value”:“SEND”,“previous_value”:“”}

Here is the last code you gave me:

export default defineComponent({
  props: {
    wordToSearchFor: {
      type: "string",
      label: "Word to Search For",
    },
    arrayToSearchThru: {
      type: "string[]",
      label: "Array to Search Through",
    },
  },
  async run({ steps, $ }) {
    const changes = steps.trigger.event.changes; // The array of changes
    const wordToSearchFor = this.wordToSearchFor; // Word to search for

    // Filter changes to find matches in the new_value field
    const results = changes
      .filter(change => {
        // Check if the word exists in the new_value
        return change.new_value && change.new_value.includes(wordToSearchFor);
      })
      .map(change => {
        // Map the results to return desired values
        const { cell, new_value, previous_value } = change;
        return { cell, new_value, previous_value };
      });

    return results; // Return the filtered and mapped results
  },
});

So what should come back is:
“cell”:“AA:57”
“new_value”:“SEND”
“previous_value”:“”},

10

To modify the code you provided so that it searches for the value “SEND” in the array and returns the specific object containing that value, you can use the following code: export default defineComponent({ props: { wordToSearchFor: { type: "string", label: "Word to Search For", }, arrayToSearchThru: { type: "string[]", label: "Array to Search Through", }, }, async run({ steps, $ }) { const changes = JSON.parse(this.arrayToSearchThru); // Parse the array of changes const wordToSearchFor = this.wordToSearchFor; // Word to search for // Find the first change that matches the word in the new_value field const result = changes.find(change => { // Check if the word exists in the new_value return change.new_value && change.new_value === wordToSearchFor; }); return result; // Return the found result }, }); ### Explanation - Parsing the Array: The arrayToSearchThru prop is expected to be a string representation of the array, so we parse it using JSON.parse. - Finding the Match: We use the find method to locate the first object in the array where the new_value matches the wordToSearchFor. - Returning the Result: The code returns the first matching object, which in this case should be {"cell":"AA:57","new_value":"SEND","previous_value":""} if “SEND” is the word you’re searching for. Make sure to provide the array as a JSON string in the arrayToSearchThru prop when using this component.