Describe the issue/error/question
OAuth2 API requires reconnect every few hours.
I have a webhook to receive data and insert into a template google spreadsheet. First I make a copy of the spreadsheet using Drive API which works without issue. Then I have a HTTP node connecting to google apis using the batchupdate and batchget functions to modify data on multiple sheets with single call. This HTTP node with OAuth API connector in the image below is the problem:
I have read other posts and see their problem was the “access_type=offline” was missing. I have this parameter set, and it still makes me reconnect every few hours.
What is the error message (if any)?
The error is:
{
“status”: “rejected”,
“reason”: {
}
}
As soon as I press reconnect it works for another few hours.
Please share the workflow
(Select the nodes and use the keyboard shortcuts CMD+C/CTRL+C and CMD+V/CTRL+V to copy and paste the workflow respectively)
`{
"nodes": [
{
"parameters": {},
"name": "Start",
"type": "n8n-nodes-base.start",
"typeVersion": 1,
"position": [
-1240,
-140
],
"disabled": true
},
{
"parameters": {
"httpMethod": "POST",
"path": "decision",
"responseMode": "lastNode",
"responseData": "allEntries",
"options": {}
},
"name": "Webhook",
"type": "n8n-nodes-base.webhook",
"typeVersion": 1,
"position": [
-1240,
140
],
"webhookId": "7c9b65e3-c50d-4482-97c8-46c9dd10d30b"
},
{
"parameters": {
"functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nlet result = [];\nlet obj = {};\nobj.json = items[0].json.body[0]\nresult.push(obj );\nreturn result;\n\n"
},
"name": "Function",
"type": "n8n-nodes-base.function",
"typeVersion": 1,
"position": [
-1020,
140
]
},
{
"parameters": {
"functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nlet simplifiedValueRanges = [];\n\nfor (item of items) {\n for (object of item.json.valueRanges) {\n let key = object.range.split(\"!\")[0];\n let valueRangeValuesObj = {};\n\n //\n\n let temporaryArray = [];\n let temporaryObject = {};\n\n for (var y = 1; y < object.values.length; y++) {\n temporaryObject = {};\n for (var i = 0; i < object.values[0].length; i++) {\n temporaryObject[object.values[0][i]] = object.values[y][i]\n }\n temporaryArray.push(temporaryObject);\n }\n\n //\n\n valueRangeValuesObj[object.range.split(\"!\")[0]] = temporaryArray;\n simplifiedValueRanges.push(valueRangeValuesObj);\n item.json.valueRanges = [];\n }\n item.json.valueRanges = simplifiedValueRanges\n}\n\n// You can write logs to the browser console\nconsole.log('Done!');\n\nreturn items;\n"
},
"name": "Convert data to objects1",
"type": "n8n-nodes-base.function",
"typeVersion": 1,
"position": [
1220,
140
]
},
{
"parameters": {
"authentication": "oAuth2",
"requestMethod": "POST",
"url": "=https://sheets.googleapis.com/v4/spreadsheets/{{$node[\"Set spreadsheet id to put data1\"].json[\"id\"]}}/values:batchUpdate",
"jsonParameters": true,
"options": {},
"bodyParametersJson": "={{$json}}"
},
"name": "Update batch google sheet1",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 1,
"position": [
280,
140
],
"credentials": {
"oAuth2Api": {
"id": "34",
"name": "Google Sheets account"
}
}
},
{
"parameters": {
"keepOnlySet": true,
"values": {
"string": [
{
"name": "template"
},
{
"name": "folder"
},
{
"name": "filename",
"value": "={{$json[\"valueRanges\"][6][\"Copy of Scenario\"][0][\"fileName\"]}} {{$json[\"valueRanges\"][6][\"Copy of Scenario\"][0][\"opportunityId\"]}} {{$json[\"valueRanges\"][6][\"Copy of Scenario\"][0][\"scenarioUid\"]}}"
}
]
},
"options": {}
},
"name": "Set template ids1",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
-800,
260
]
},
{
"parameters": {
"authentication": "oAuth2",
"url": "=https://sheets.googleapis.com/v4/spreadsheets/{{$json[\"spreadsheetId\"]}}/values:batchGet?ranges={{$json[\"Copy of address\"]}}&ranges={{$json[\"Copy of administrator\"]}}&ranges={{$json[\"Copy of assumptions\"]}}&ranges={{$json[\"Copy of borrower\"]}}&ranges={{$json[\"Copy of coborrower\"]}}&ranges={{$json[\"Copy of company\"]}}&ranges={{$json[\"Copy of contacts\"]}}&ranges={{$json[\"Copy of data\"]}}&ranges={{$json[\"Copy of financials\"]}}&ranges={{$json[\"Copy of income\"]}}&ranges={{$json[\"Copy of liabilities\"]}}&ranges={{$json[\"Copy of mortgage\"]}}&ranges={{$json[\"Copy of property\"]}}",
"options": {},
"queryParametersUi": {
"parameter": []
}
},
"name": "Get batch google sheet",
"type": "n8n-nodes-base.httpRequest",
"typeVersion": 1,
"position": [
1040,
140
],
"credentials": {
"oAuth2Api": {
"id": "34",
"name": "Google Sheets account"
}
}
},
{
"parameters": {
"options": {}
},
"name": "Set Hold ",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
-440,
20
]
},
{
"parameters": {
"options": {}
},
"name": "Set Hold 3",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
-80,
140
]
},
{
"parameters": {
"functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nlet simplifiedValueRanges = [];\n\nfor (item of items) {\n for (object of item.json.valueRanges) {\n let valueRangeValuesObj = {};\n valueRangeValuesObj.range = Object.keys(object)[0];\n\n if (object[Object.keys(object)[0]].length > 0) {\n let resultArray = [];\n let temporaryArray = [];\n if (resultArray.length < 1) {\n let mainKeys = Object.keys(object[Object.keys(object)[0]][0]);\n resultArray.push(mainKeys);\n }\n for (let j = 0; j < object[Object.keys(object)[0]].length; j++) {\n let parent = object[Object.keys(object)[0]][j];\n for (let p = 0; p < Object.keys(parent).length; p++) {\n temporaryArray.push(parent[resultArray[0][p]] ?? \"\");\n }\n resultArray.push(temporaryArray);\n temporaryArray = [];\n }\n\n //\n valueRangeValuesObj.values = resultArray;\n\n simplifiedValueRanges.push(valueRangeValuesObj);\n item.json.valueRanges = [];\n }\n }\n delete item.json.valueRanges;\n delete item.json.spreadsheetId;\n \n item.json.valueInputOption= \"USER_ENTERED\";\n item.json.data= simplifiedValueRanges;\n}\n\n// You can write logs to the browser console\nconsole.log('Done!');\n\nreturn items;\n"
},
"name": "Convert scenario to array1",
"type": "n8n-nodes-base.function",
"typeVersion": 1,
"position": [
-620,
20
]
},
{
"parameters": {
"authentication": "oAuth2",
"operation": "copy",
"fileId": "={{$node[\"Set template ids1\"].json[\"template\"]}}",
"options": {
"name": "={{$node[\"Set template ids1\"].json[\"filename\"]}}",
"parents": [
"={{$json[\"folder\"]}}"
]
}
},
"name": "Google copy spreadsheet1",
"type": "n8n-nodes-base.googleDrive",
"typeVersion": 1,
"position": [
-620,
260
],
"executeOnce": true,
"credentials": {
"googleDriveOAuth2Api": {
"id": "30",
"name": "Google Drive account"
}
}
},
{
"parameters": {
"keepOnlySet": true,
"values": {
"string": [
{
"name": "Copy of address",
"value": "Copy of address"
},
{
"name": "Copy of assumptions",
"value": "Copy of assumptions"
},
{
"name": "Copy of borrower",
"value": "Copy of borrower"
},
{
"name": "Copy of coborrower",
"value": "Copy of coborrower"
},
{
"name": "Copy of contacts",
"value": "Copy of contacts"
},
{
"name": "Copy of income",
"value": "Copy of income"
},
{
"name": "Copy of liabilities",
"value": "Copy of liabilities"
},
{
"name": "Copy of mortgage",
"value": "Copy of mortgage"
},
{
"name": "Copy of property",
"value": "Copy of property"
},
{
"name": "Copy of company",
"value": "Copy of company"
},
{
"name": "Copy of financials",
"value": "Copy of financials"
},
{
"name": "Copy of administrator",
"value": "Copy of administrator"
},
{
"name": "Copy of data",
"value": "Copy of data"
}
]
},
"options": {}
},
"name": "Set sheet ranges1",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
480,
260
]
},
{
"parameters": {
"keepOnlySet": true,
"values": {
"string": [
{
"name": "spreadsheetId",
"value": "={{$json[\"spreadsheetId\"]}}"
}
]
},
"options": {}
},
"name": "Set spreadsheet to get data1",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
480,
20
]
},
{
"parameters": {
"keepOnlySet": true,
"values": {
"string": [
{
"name": "id",
"value": "={{$json[\"id\"]}}"
}
]
},
"options": {}
},
"name": "Set spreadsheet id to put data1",
"type": "n8n-nodes-base.set",
"typeVersion": 1,
"position": [
-440,
260
]
},
{
"parameters": {
"mode": "passThrough"
},
"name": "Merge",
"type": "n8n-nodes-base.merge",
"typeVersion": 1,
"position": [
-260,
140
]
},
{
"parameters": {
"mode": "mergeByIndex"
},
"name": "Merge ",
"type": "n8n-nodes-base.merge",
"typeVersion": 1,
"position": [
680,
140
]
},
{
"parameters": {
"unit": "seconds"
},
"name": "Wait 1 create1",
"type": "n8n-nodes-base.wait",
"typeVersion": 1,
"position": [
100,
140
],
"webhookId": "16606c33-24a6-4261-b1fd-5da3e7dc5e4e"
},
{
"parameters": {
"unit": "seconds"
},
"name": "Wait 1 calcs1",
"type": "n8n-nodes-base.wait",
"typeVersion": 1,
"position": [
860,
140
],
"webhookId": "c3cd5a9c-776f-4929-add4-88241e1c4fea"
}
],
"connections": {
"Webhook": {
"main": [
[
{
"node": "Function",
"type": "main",
"index": 0
}
]
]
},
"Function": {
"main": [
[
{
"node": "Set template ids1",
"type": "main",
"index": 0
},
{
"node": "Convert scenario to array1",
"type": "main",
"index": 0
}
]
]
},
"Update batch google sheet1": {
"main": [
[
{
"node": "Set sheet ranges1",
"type": "main",
"index": 0
},
{
"node": "Set spreadsheet to get data1",
"type": "main",
"index": 0
}
]
]
},
"Set template ids1": {
"main": [
[
{
"node": "Google copy spreadsheet1",
"type": "main",
"index": 0
}
]
]
},
"Get batch google sheet": {
"main": [
[
{
"node": "Convert data to objects1",
"type": "main",
"index": 0
}
]
]
},
"Set Hold ": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 0
}
]
]
},
"Set Hold 3": {
"main": [
[
{
"node": "Wait 1 create1",
"type": "main",
"index": 0
}
]
]
},
"Convert scenario to array1": {
"main": [
[
{
"node": "Set Hold ",
"type": "main",
"index": 0
}
]
]
},
"Google copy spreadsheet1": {
"main": [
[
{
"node": "Set spreadsheet id to put data1",
"type": "main",
"index": 0
}
]
]
},
"Set sheet ranges1": {
"main": [
[
{
"node": "Merge ",
"type": "main",
"index": 1
}
]
]
},
"Set spreadsheet to get data1": {
"main": [
[
{
"node": "Merge ",
"type": "main",
"index": 0
}
]
]
},
"Set spreadsheet id to put data1": {
"main": [
[
{
"node": "Merge",
"type": "main",
"index": 1
}
]
]
},
"Merge": {
"main": [
[
{
"node": "Set Hold 3",
"type": "main",
"index": 0
}
]
]
},
"Merge ": {
"main": [
[
{
"node": "Wait 1 calcs1",
"type": "main",
"index": 0
}
]
]
},
"Wait 1 create1": {
"main": [
[
{
"node": "Update batch google sheet1",
"type": "main",
"index": 0
}
]
]
},
"Wait 1 calcs1": {
"main": [
[
{
"node": "Get batch google sheet",
"type": "main",
"index": 0
}
]
]
}
}
}
``
## Share the output returned by the last node
<!-- If you need help with transforming the data, please also share the expected output -->
## Information on your n8n setup
- **n8n version:** Cloud
- **Database you're using (default: SQLite):**
- **Running n8n with the execution process [own(default), main]:**
- **Running n8n via [Docker, npm, n8n.cloud, desktop app]:**
