Google sheets append

Questions
1

I’m having some trouble figuring out how to pipe the data into google sheets.

I’m coming from the XML to JSON node and the rows i’m trying to use in google sheets would be:

$data[“serv:message”][“serv:body”][“serv:bodyContent”][“use:user”][“use:userId”]
$data[“serv:message”][“serv:body”][“serv:bodyContent”][“use:user”][“use:name”]

where use:user is the array that the google sheets node is supposed to magically know how to iterate over.

do I need a Set step in between, or a function / or function item?

2

If “use:user” is an array then the only choice you currently have is a Function-Node. In that node you would have to iterates over the items in “use:user” and return for each of it a key: value pair of the data that should be appended.

So the data you return in the node would have to have this format:

return [
  // First array item of “use:user”
  {
    json: {
      nameOfFirstRow: "value to set in first row",
      nameOfSecondRow: "value to set in second row"
  },
  // Second array item of “use:user”
  {
    json: {
      nameOfFirstRow: "value to set in first row",
      nameOfSecondRow: "value to set in second row"
    }
  }
]

The above data would then append two rows to the Google Sheet.

I hope that helps. If not, simply post example data that gets currently output by your node and I can then create the Function-Node for you.

3

I would like to append multiple row and pipe the data in a google table from a HTTP request.

The workflow is working but as expected, only one by one row is filled per execution.

The HTTP request gets all the activities from an outreach campaign and pipe the data in a google sheet.

The aim of this workflow is to generate a report in Google data studio using the activities from the campaignId, available in the google sheet.

Output Data
Gender {{$node[“HTTP Request”].json[“0”][“Gender”]}}
CampaignName {{$node[“HTTP Request”].json[“0”][“campaignName”]}}
CompanyName {{$node[“HTTP Request”].json[“0”][“companyName”]}}
CreatedAt {{$node[“HTTP Request”].json[“0”][“createdAt”]}}
Current_Job_Title {{$node[“HTTP Request”].json[“0”][“current_job_title”]}}
leadEmail {{$node[“HTTP Request”].json[“0”][“email”]}}
leadFirstName {{$node[“HTTP Request”].json[“0”][“leadFirstName”]}}
LeadLastName {{$node[“HTTP Request”].json[“0”][“leadLastName”]}}
linkedinUrl {{$node[“HTTP Request”].json[“0”][“linkedinUrl”]}}
sendUserEmail {{$node[“HTTP Request”].json[“0”][“sendUserEmail”]}}
Type {{$node[“HTTP Request”].json[“0”][“type”]}}
isFirst {{$node[“HTTP Request”].json[“0”][“isFirst”]}}

Do I need to use a function node to append multiple row in the google sheet ?

It might be tricky because it can be from 1 to 100 rows at each launch?
I set up a cron node in order to trigger the workflow two times a day.

image
image902×245 13.6 KB

Any advices ?

Thanks a lot!

4

You can have a look at this JavaScript Snippet:

If you add a Function-Node with that code directly after the HTTP Request node it will split them out into different items.

You then just have to adjust your expression from for example
{{$node["HTTP Request"].json["0"]["Gender"]}} to {{$json["Gender"]}} and it should work fine.

6

Hi Jan, we hope that you well, please help us, we are trying to append a json to a Google Sheet it say that the work flow run ok but on the sheet is not data.

this is the output on n8n of the data that we are sending:

[
  [
    {
      "values": {
        "line": 0,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "109EE1K011",
        "nombre_producto": "Polera de manga larga con cuello henley - Talla : S",
        "ean13": "",
        "precio_neto": "14277.000000",
        "cantidad": "1"
      }
    },
    {
      "values": {
        "line": 1,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "109EE1K011",
        "nombre_producto": "Polera de manga larga con cuello henley - Talla : XL",
        "ean13": "",
        "precio_neto": "14277.000000",
        "cantidad": "1"
      }
    },
    {
      "values": {
        "line": 2,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "curva_1_arrow",
        "nombre_producto": "Curva Nueva temporada Arrow",
        "ean13": "",
        "precio_neto": "1352584.000000",
        "cantidad": "1"
      }
    },
    {
      "values": {
        "line": 3,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2215WNB",
        "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-S",
        "ean13": "",
        "precio_neto": 319243.69748000003,
        "cantidad": 10
      }
    },
    {
      "values": {
        "line": 4,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2215WNB",
        "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-M",
        "ean13": "",
        "precio_neto": 159621.84874000002,
        "cantidad": 5
      }
    },
    {
      "values": {
        "line": 5,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2215WNB",
        "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-L",
        "ean13": "",
        "precio_neto": 159621.84874000002,
        "cantidad": 5
      }
    },
    {
      "values": {
        "line": 6,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2215WNB",
        "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-XL",
        "ean13": "",
        "precio_neto": 95773.109244,
        "cantidad": 3
      }
    },
    {
      "values": {
        "line": 7,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2117SCE",
        "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-S",
        "ean13": "",
        "precio_neto": 268840.33613,
        "cantidad": 10
      }
    },
    {
      "values": {
        "line": 8,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2117SCE",
        "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-M",
        "ean13": "",
        "precio_neto": 134420.168065,
        "cantidad": 5
      }
    },
    {
      "values": {
        "line": 9,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2117SCE",
        "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-L",
        "ean13": "",
        "precio_neto": 134420.168065,
        "cantidad": 5
      }
    },
    {
      "values": {
        "line": 10,
        "id_orden": 32,
        "referencia_orden": "HSKPEBLOL",
        "fecha_orden": "2021-07-22 16:40:03",
        "nombre_cliente": "Austral",
        "rut": "76856645-3",
        "dir_alias": "Austral",
        "direccion": "Los angeles",
        "ciudad": "Los angeles",
        "referencia_producto": "CM2117SCE",
        "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-XL",
        "ean13": "",
        "precio_neto": 80652.10083899999,
        "cantidad": 3
      }
    }
  ]
]
7

Hey @Msaustral!

You will have to use the Function node to transform the data structure. Currently your data follows the below structure

[
  [
    {
     item-1
    },
   {
     item-2
    },
   ...
  ]
]

For n8n this is a single item. You will have to convert the data into the following structure

[
  {
  item-1
  },
  {
  item-2
  },
 ...
]

You can use the code snippet mentioned here to convert the structure: JavaScript Code Snippets | Docs

Note: Make sure the field names in your data is exactly the same as the column name in Google Sheet

8

Hi thank you for the answer, but it does not work :pensive:

this is my body:

{
  "data": [
    {
      "line": 0,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "109EE1K011",
      "nombre_producto": "Polera de manga larga con cuello henley - Talla : S",
      "ean13": "",
      "precio_neto": "14277.000000",
      "cantidad": "1"
    },
    {
      "line": 1,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "109EE1K011",
      "nombre_producto": "Polera de manga larga con cuello henley - Talla : XL",
      "ean13": "",
      "precio_neto": "14277.000000",
      "cantidad": "1"
    },
    {
      "line": 2,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "curva_1_arrow",
      "nombre_producto": "Curva Nueva temporada Arrow",
      "ean13": "",
      "precio_neto": "1352584.000000",
      "cantidad": "1"
    },
    {
      "line": 3,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2215WNB",
      "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-S",
      "ean13": "",
      "precio_neto": 319243.69748000003,
      "cantidad": 10
    },
    {
      "line": 4,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2215WNB",
      "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-M",
      "ean13": "",
      "precio_neto": 159621.84874000002,
      "cantidad": 5
    },
    {
      "line": 5,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2215WNB",
      "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-L",
      "ean13": "",
      "precio_neto": 159621.84874000002,
      "cantidad": 5
    },
    {
      "line": 6,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2215WNB",
      "nombre_producto": "CAMISA SPANDEX COTELE ARROW\n Talla-XL",
      "ean13": "",
      "precio_neto": 95773.109244,
      "cantidad": 3
    },
    {
      "line": 7,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2117SCE",
      "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-S",
      "ean13": "",
      "precio_neto": 268840.33613,
      "cantidad": 10
    },
    {
      "line": 8,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2117SCE",
      "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-M",
      "ean13": "",
      "precio_neto": 134420.168065,
      "cantidad": 5
    },
    {
      "line": 9,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2117SCE",
      "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-L",
      "ean13": "",
      "precio_neto": 134420.168065,
      "cantidad": 5
    },
    {
      "line": 10,
      "id_orden": 32,
      "referencia_orden": "HSKPEBLOL",
      "fecha_orden": "2021-07-22 16:40:03",
      "nombre_cliente": "Austral",
      "rut": "76856645-3",
      "dir_alias": "Austral",
      "direccion": "Los angeles",
      "ciudad": "Los angeles",
      "referencia_producto": "CM2117SCE",
      "nombre_producto": "CAMISA CASUAL SPANDEX FLOREADA\n Talla-XL",
      "ean13": "",
      "precio_neto": 80652.10083899999,
      "cantidad": 3
    }
  ]
}

I did a function:

var items = $node["Webhook"].json["body"]["data"];
return items.json.map(item => {
  return {
    json: item
  }
});

I get and error Cannot read property ‘map’ of undefined

var items = $node["Webhook"].json["body"]["data"];
var data = [];
var products = [];
  items.forEach(function (linea, index) {
    products.push({});
    products[products.length-1] = {};
    products[products.length-1].line = linea.line;
  });
data.push({json:
  {
    products
  }
});
return data;

I get:

[
  [
    {
      "json": {
        "products": [
          {
            "line": 0
          },
          {
            "line": 1
          },
          {
            "line": 2
          },
          {
            "line": 3
          },
          {
            "line": 4
          },
          {
            "line": 5
          },
          {
            "line": 6
          },
          {
            "line": 7
          },
          {
            "line": 8
          },
          {
            "line": 9
          },
          {
            "line": 10
          }
        ]
      }
    }
  ]
]

I did a function:

var items = $node["Webhook"].json["body"]["data"];
var products = [];
  items.forEach(function (linea, index) {
    products.push({});
    products[products.length-1] = {};
    products[products.length-1].line = linea.line;
  });
return products;

I get:

[
  [
    {
      "line": 0
    },
    {
      "line": 1
    },
    {
      "line": 2
    },
    {
      "line": 3
    },
    {
      "line": 4
    },
    {
      "line": 5
    },
    {
      "line": 6
    },
    {
      "line": 7
    },
    {
      "line": 8
    },
    {
      "line": 9
    },
    {
      "line": 10
    }
  ]
]
9

The following code snippet should work

return items[0].json.data.map(item => {
  return {
    json: item
  }
});
10

ERROR: Cannot read property ‘map’ of undefined

11

DONE:

return $node["Webhook"].json["body"]["data"].map(item => {
  return {
    json: item
  }
});