Output processed entries into multiple rows in a worksheet

Help
1

Hello there!

We need to write a set of data as an output to a worksheet in Google spreadsheet as given in the example:

These are set of data from the previous step.

{
  'Operating Unit (Credit)': 'ABC Company (ABC)',
  'Operating Unit (Credit) Subsidiary Internal ID': '',
  'Operating Unit (debit)': 'DEF Company (DEF)',
  'Operating Unit (debit) Subsidiary Internal ID': '',
  'Account (Credit)': '1190',
  'Account (Debit)': '2190',
  'Amount(Debit)': 4752020,
  'Amount(Credit)': 4752020,
  Memo: '1190/2190 clearing',
  'Name (Rep Vendor) (Credit)': 'ZZZ-DEF Company (DEF)',
  'Name (Rep Vendor) (debit)': 'ZZZ-ABC Company (ABC)',
  'Due To/From subsidiary (Credit)': 'ABC Company (ABC)',
  'Due To/From subsidiary (debit)': 'ABC Company (ABC)',
  'Rate (Credit)': 0.0016196228130352986,
  'Rate (debit)': 0.0016196228130352986
},
{
  'Operating Unit (Credit)': 'GHI Company (GHI)',
  'Operating Unit (Credit) Subsidiary Internal ID': '',
  'Operating Unit (debit)': 'DEF Company (DEF)',
  'Operating Unit (debit) Subsidiary Internal ID': '',
  'Account (Credit)': '1190',
  'Account (Debit)': '2190',
  'Amount(Debit)': 170200,
  'Amount(Credit)': 170200,
  Memo: '1190/2190 clearing',
  'Name (Rep Vendor) (Credit)': 'ZZZ-DEF Company (DEF)',
  'Name (Rep Vendor) (debit)': 'ZZZ-GHI Company (GHI)',
  'Due To/From subsidiary (Credit)': 'DEF Company (DEF)',
  'Due To/From subsidiary (debit)': 'GHI Company (GHI)',
  'Rate (Credit)': 0.0015990011750881316,
  'Rate (debit)': 0.0015990011750881316
}
...
...

Currently it puts all the data in a single row. But, for every set of row above, we want to have two rows in the output worksheet as below.

Can I get help here?

"Operating Unit", "Account", "Amount (Debit)", "Amount (Credit)","Memo", "Name","Due To/From Subsidiary", "Rate"
'ABC Company (ABC)',1190,0.00,4752020,'1190/2190 clearing','ZZZ-DEF Company (DEF)',0.0016196228130352986
'DEF Company (DEF)',2190,4752020,0.00,'1190/2190 clearing','ZZZ-ABC Company (ABC)',0.0016196228130352986
'GHI Company (GHI)',1190,0.00,170200,'1190/2190 clearing','ZZZ-DEF Company (DEF)',0.0015990011750881316
'DEF Company (DEF)',2190,170200,0.00,'1190/2190 clearing','ZZZ-GHI Company (GHI)',0.0015990011750881316

Advance thanks!

Regards,

Natarajan

2

Hi @natarajan_s,

Absolutely, I can help you with this!

Goal

You want to transform each object in your data array into two rows for Google Sheets, each with a specific set of columns, and then write all those rows to a worksheet.

Approach

  1. Transform the data: For each object, create two rows:
    • One for the “credit” side (with Amount (Credit) filled, Amount (Debit) = 0).
    • One for the “debit” side (with Amount (Debit) filled, Amount (Credit) = 0).
  2. Format the data to match your desired columns.
  3. Write each row to Google Sheets (using the “Add Single Row” action, or batch if available).

Below is a Pipedream Node.js code step that takes an array of objects (like your example), transforms them as described, and returns the array of rows ready to be sent to Google Sheets.

export default defineComponent({
  props: {
    // If your data comes from a previous step, you can use a prop or reference steps.prev_step_name
    inputData: {
      type: "string[]", // or "object[]" if you pass the array directly
      label: "Input Data",
      description: "Array of data objects to transform",
    },
  },
  async run({ steps, $ }) {
    // If inputData is a string[], parse each item as JSON
    const dataArray = this.inputData.map(item =>
      typeof item === "string" ? JSON.parse(item) : item
    );

    // Define the columns in order
    const columns = [
      "Operating Unit",
      "Account",
      "Amount (Debit)",
      "Amount (Credit)",
      "Memo",
      "Name",
      "Due To/From Subsidiary",
      "Rate",
    ];

    // Transform data
    const rows = [];
    for (const entry of dataArray) {
      // Credit row
      rows.push({
        "Operating Unit": entry["Operating Unit (Credit)"],
        "Account": entry["Account (Credit)"],
        "Amount (Debit)": 0,
        "Amount (Credit)": entry["Amount(Credit)"],
        "Memo": entry["Memo"],
        "Name": entry["Name (Rep Vendor) (Credit)"],
        "Due To/From Subsidiary": entry["Due To/From subsidiary (Credit)"],
        "Rate": entry["Rate (Credit)"],
      });
      // Debit row
      rows.push({
        "Operating Unit": entry["Operating Unit (debit)"],
        "Account": entry["Account (Debit)"],
        "Amount (Debit)": entry["Amount(Debit)"],
        "Amount (Credit)": 0,
        "Memo": entry["Memo"],
        "Name": entry["Name (Rep Vendor) (debit)"],
        "Due To/From Subsidiary": entry["Due To/From subsidiary (debit)"],
        "Rate": entry["Rate (debit)"],
      });
    }

    // Optionally, return as array of arrays (for Google Sheets)
    const rowsArray = rows.map(row =>
      columns.map(col => row[col])
    );

    // Return both object and array formats for flexibility
    return {
      columns,
      rows,      // array of objects
      rowsArray, // array of arrays (for Google Sheets)
    };
  },
});

Next Steps

  • Add this code step before your Google Sheets step.
  • In your Google Sheets action, loop over steps.YOUR_STEP_NAME.rows or rowsArray and add each row to the sheet.
  • If using the “Add Single Row” action, you may need to add a step for each row, or use a loop (with Looping).

Google Sheets Integration

You can use the Google Sheets “Add Single Row” action and map each value from the transformed row to the appropriate column.

Let me know if you need a full workflow example, or help with the Google Sheets step!

I’m a bot powered by Pipedream and GPT-4. I’m still learning, so please double-check my answers and code! Learn how to build your own.