Changing an excel sheet to a certain format

Tips & Tricks
1

I would like to build an AI agent flow like this, is it hard?

  1. When a excel sheet is placed in a google drive
  2. The data from the excel sheet gets correctely placed in a new excel file with the right data under the right column
  3. Based in the purchase price in the sheet, calculate what our selling price could be with a 40% margin
  4. Send a email with the KPIs to XYC email adress:
2

Can you share an example how the initial excel sheet looks like. Also is the excel sheet always in the same format, i.e. same column names?

3

Of course:

This is an example:

And this is the output prefered to sort it into:

4

Shouldn’t be too difficult. ChatGPT suggested this. You’ll obviously want to check the details :slight_smile:

Step-by-Step Node Setup:

1. Google Drive Trigger (Watch New Files)

  • Trigger Type: Watch
  • Folder: Select the Google Drive folder where “Forum Offer” will be uploaded.
  • Polling Interval: Adjust as needed (e.g., every 5 minutes).

2. Google Drive Node (Download File)

  • Operation: Download File
  • File ID: Use {{$json["id"]}} from the trigger node.

3. Spreadsheet File Node (Read Data)

  • Operation: Read from file
  • File Format: CSV
  • File Path: Use output from the previous node ({{$json["path"]}}).
  • Header Row: Enabled.

4. Set Node (Transform Data)

  • Use Expressions to Map Data:
    • "Item #" → {{$index + 1}}
    • "Man, Woman Unisex, Kids" → {{$json["Gender"]}}
    • "Brand" → {{$json["Brand"]}}
    • "Collection Name Group" → "" (empty)
    • "Product\ncategory" → {{$json["Type"]}}
    • "Article Name" → {{$json["Style Name"]}}
    • "Article Number" → {{$json["Style Number"]}}
    • "Color code" → ""
    • "Color name" → {{$json["Color Combination"]}}
    • "Barcodes" → {{$json["EAN Code"]}}
    • "Article Description text" → {{$json["Type"]}} + " - " + {{$json["Composition"]}}
    • "Size" → {{$json["Size EU"]}}
    • "Supplier Stock" → {{$json["Stock"]}}
    • "Purchase price" → {{$json["Wholesale Price, Eur"]}}
    • "Recommended Retail Price" → {{$json["Retail Price, Eur"]}}
    • "Sizeguide Link" → ""
    • "Stock API Link (Optional)" → ""
    • "Product Picture Link (Optional)" → ""
    • "Sets" → {{$json["Number of pairs in set"]}}

5. Spreadsheet File Node (Write Data)

  • Operation: Write to file
  • File Format: CSV
  • File Name: "Forum Output - Processed.csv"
  • Output Columns: Set manually based on the transformed data.

6. Google Drive Node (Upload Transformed File)

  • Operation: Upload
  • Destination Folder: Choose where to store the transformed file.
  • File Name: "Forum Output - Processed.csv"
  • Binary Data Input: Enable and use the previous node’s output.
5

If the original file is diffrent each time? is it possible to have the AI agent dynamic so it can adapt and still fill in the second file? output?

6

That gets quite a bit more tricky. If you have 500 row excel sheet you can’t put AI to actually generate the output.

AI might be able to figure out which column name from the input should go to which in the output excel, but I would aim to avoid using AI as it might mess up part of the time.

I would try to figure out all the possible input formats, get a sample of each and use AI to write a code node that outputs the correct expressions regardless of which input it is.

Maybe someone else has a better idea.

7

Thank you so much for the reply there, trying to find the best way forward