Hi, I have a problem with the date conversion retrieved from the database. The date is taken with the ISO 8601 format.
I want this format to be as follows:
2020-11-28T12: 32: 26.000Z. -------> 2020-11-28 12:32
I used the “Set” module and added:
Values to Set:
String:
Name: user_registered
Value: {{$json["user_registered"].replace("T", " ").replace(".000Z", "").substr(0, 16)}}
Unfortunately, the user_registered cell is empty when importing to GSheets.
When I first execute “Set” which will return the arrays correctly and then execute “append: sheets” everything is ok.
My workflow:
{
"name": "MySQL -> Google Sheets",
"nodes": [
{
"parameters": {},
"name": "Start",
"type": "n8n-nodes-base.start",
"typeVersion": 1,
"position": [
250,
300
],
"alwaysOutputData": false
},
{
"parameters": {
"operation": "executeQuery",
"query": "=SELECT ID, user_login, user_email, user_registered, display_name FROM wp_users"
},
"name": "MySQL",
"type": "n8n-nodes-base.mySql",
"typeVersion": 1,
"position": [
470,
300
],
"alwaysOutputData": true,
"credentials": {
"mySql": "MySQL"
}
},
{
"parameters": {
"operation": "append",
"sheetId": "10rtXQerHJaSx7CMt******************",
"range": "=A:E",
"options": {
"valueInputMode": "USER_ENTERED"
}
},
"name": "Google Sheets",
"type": "n8n-nodes-base.googleSheets",
"typeVersion": 1,
"position": [
870,
300
],
"alwaysOutputData": true,
"credentials": {
"googleApi": "Google Services"
}
},
{
"parameters": {
"keepOnlySet": true,
"values": {
"string": [
{
"name": "ID",
"value": "={{$json[\"ID\"]}}"
},
{
"name": "user_login",
"value": "={{$json[\"user_login\"]}}"
},
{
"name": "user_email",
"value": "={{$json[\"user_email\"]}}"
},
{
"name": "user_registered",
"value": "={{$json[\"user_registered\"].replace(\"T\", \" \").replace(\".000Z\", \"\").substr(0, 16)}}"
},
{
"name": "display_name",
"value": "={{$json[\"display_name\"]}}"
}
],
"number": []
},
"options": {
"dotNotation": false
}
},
"name": "Set",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
670,
300
],
"alwaysOutputData": true
}
],
"connections": {
"Start": {
"main": [
[
{
"node": "MySQL",
"type": "main",
"index": 0
}
]
]
},
"MySQL": {
"main": [
[
{
"node": "Set",
"type": "main",
"index": 0
}
]
]
},
"Set": {
"main": [
[
{
"node": "Google Sheets",
"type": "main",
"index": 0
}
]
]
}
},
"active": false,
"settings": {
"timezone": "Europe/Warsaw"
},
"id": "1"
}