Filter g sheet data, Move to a new sheet and in the process add some data

[LucasOneSouth]

Hi everyone

I’m working on a new custom workflow in N8N for a sales funnel.

I have a Google Sheet that receives new orders periodically, and I want to monitor it for new “confirmed” orders and move them to a second sheet while also adding a timestamp and event ID to each order in a specific format.

Here’s a sample of the data I’m receiving in the Google Sheets for each order:

{
  "created": "2023-02-01 15:59:01",
  "offer_name": "xxxxxxxxxxx",
  "order_id": "119192993",
  "Status": "confirmed",
  "price": "13,5",
  "Nombre": "testing fields",
  "Email": "[email protected]",
  "Teléfono": "5556398",
  "ip": "181.23.84.248",
  "city": "La Plata",
  "region": "Buenos Aires",
  "statecode": "B",
  "countrycode": "AR",
  "code": "ok",
  "msg": "Order saved",
  "is_double": "",
  "goods_id": "2",
  "warning": "Need to add terms & conditions.",
  "subacc_1": "",
  "subacc_2": "",
  "subacc_3": "",
  "subacc_4": "",
  "utm_campaign": "campaign",
  "utm_content": "content",
  "utm_medium": "medium",
  "utm_source": "source",
  "utm_term": "term",
  "_fbp": "fb.1.1675618747490.1769344468",
  "_fbc": "fb.1.1675620351469.IwAR1y3BbjIujGLZ1QZC_lgNniJOlPG-s5d8s6Gx9gAH4_vawXVKrAFUnXd9s",
  "ext_in_id": "123456"
}

Each order is a separate item in the Google sheet node, which is set to monitor the sheet every five minutes.

Here are the steps I need the workflow to do:

1. Get data from the Google Sheet every 5 minutes.
2. Filter for orders that have a “confirmed” status.
3. Generate a timestamp and an event ID for each order.
4. Add the timestamp and event ID to each order as new columns.
5. Move the filtered orders to a second sheet.

So far this is what I done:

1. Set the Google Sheet node to monitor new or updated rows every five minutes.
2. Set a filter node to only keep the orders whose status is “confirmed”.
3. Use the code node to generate a timestamp and an event ID with the code provided below.

Here’s how the workflow looks like

This is the code the I did use in the code node to generate time stamp and Event_ID:

// Define a function to create an object with a UNIX timestamp for the current time minus 10 minutes
function createTimestampObject() {
  const now = new Date();
  const tenMinutesAgo = new Date(now.getTime() - 10 * 60 * 1000);
  const unixTime = Math.floor(tenMinutesAgo.getTime() / 1000);
  return { timestamp: unixTime };
}

// Call the createTimestampObject function to get a timestamp object for the current time minus 10 minutes
const timestampObject = createTimestampObject();

// Convert the UNIX timestamp to a string in UNIX format
const timestampString = timestampObject.timestamp.toString();

// Define a function to generate an ID
function generateId() {
  const id = `${Math.floor(Math.random() * 1000000)}_${Math.floor(Math.random() * 1000000)}_enter`;
  return id;
}

// Call the generateId function to get an ID
const id = generateId();

// Return the timestamp string and the ID
return { timestamp: timestampString, id: id };```

The code is working but only when I run the code node to run once for all items.

in that scenario, the output looks something like this:

but, when I do run the function for each item I do get this error

The problem I’m having is that I’m getting that error when I try to run the code node for each item, while I want to add the generated timestamp and event ID to each item before moving them to the second sheet.

Can anybody help me iterate the code so that I can add a timestamp and event ID to each Google sheet item before adding it to the second sheet with the filtered data?

Thanks on advance!

Cheers!

[Jon]

Hey @LucasOneSouth,

Give the below a bash, When using Run Once for Each Item you need to return the data in a specific way { json: { x:y } }

// Define a function to create an object with a UNIX timestamp for the current time minus 10 minutes
function createTimestampObject() {
  const now = new Date();
  const tenMinutesAgo = new Date(now.getTime() - 10 * 60 * 1000);
  const unixTime = Math.floor(tenMinutesAgo.getTime() / 1000);
  return { timestamp: unixTime };
}

// Call the createTimestampObject function to get a timestamp object for the current time minus 10 minutes
const timestampObject = createTimestampObject();

// Convert the UNIX timestamp to a string in UNIX format
const timestampString = timestampObject.timestamp.toString();

// Define a function to generate an ID
function generateId() {
  const id = `${Math.floor(Math.random() * 1000000)}_${Math.floor(Math.random() * 1000000)}_enter`;
  return id;
}

// Call the generateId function to get an ID
const id = generateId();

// Return the timestamp string and the ID
return { json: { timestamp: timestampString, id: id } };

[LucasOneSouth]

hey @Jon

That works! thank you so much.

I have a similar scenario in the context of a Facebook conversion API workflow

You can check it here if you like

Facebook Conversion API Project

Hey everyone I’m working on this workflow to send purchase events to Facebook Conversion API . Here’s a screenshot on how it looks so far. [Captura de pantalla 2023-03-08 a la(s) 13.56.37] Let me explain the steps: Google Sheet looks for new rows added with data every two minutes (new purchase). [Captura de pantalla 2023-03-08 a la(s) 13.05.25] Set node filter data to keep data needed for Facebook into 1 item [Captura de pantalla 2023-03-08 a la(s) 13.06.33…

cheers!