OAuth2 API requires reconnection every few hours for Google APIs?

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]:**

Hi @markhanson, I am sorry to hear you’re having this problem. I’ve tested out my trusty old Google OAuth2 credentials from last year, but they are still working as expected and didn’t expire.

So I wonder if the problem here could be slightly different. Are you using the latest version of n8n? Since version 0.156.0 you should see the error response body in the UI when opening the node that failed to execute. Could you check the node for a more detailed error message?

Hi @MutedJam thanks for the reply! The version was Running version [email protected] on cloud. I just changed to the latest Running version [email protected] I will see if this resolves it overnight. I ran the workflow and got the same error. I reconnected and wait to see what happens. Thanks Mark

Hello @MutedJam

Unfortunately updating n8n version did not resolve the issue. OAuth2 API still requires reconnect every few hours.

Any other ideas?

Hi @markhanson, thanks for checking this and sharing your screenshot - from looking at it, it seems the request did not get a response at all and failed beforehand.

Is there a chance one of the expressions used in your HTTP Request node resolve to an invalid value occasionally (= not for all items, but for some of them)?

The Google nodes are using a different authorization URL than you are. Change it to https://accounts.google.com/o/oauth2/v2/auth. Also, you are setting the authentication to the header, but it should be in the body. And finally, add prompt consent to the Auth URI Query Parameters access_type=offline&prompt=consent

1 Like

Everything works if I reconnect, so I dont think there is anything broken. I added Auth URI Query Parameters … &access_type=offline&approval_prompt=force … I will wait to see what this does and then try something else. Thanks

Thanks for the help. I will try this soon and update the outcome. I have 2 running now, the one you have given me and the one google consol gives me. Both work okay. We see in the morning which one is still alive. Thanks!

1 Like

Hi @RicardoE105, @MutedJam,

Both connections above have not disconnected over the weekend. I am taking a wild guess that the approval_prompt= parameter was required as the Header/Body or AuthURL V2 didn’t make any difference. I am not sure what is the difference between approval_prompt=force and approval_prompt=consent? But both work ok. and both URLs work also.
Thanks
Mark

1 Like

Hello @MutedJam @RicardoE105

Unfortunately both implementationa above required reauthentication exactly 7 days after I set them last week. I will make a ticket on google and see what they say.

Nice weekend!

Hey @markhanson, are you by any chance using a test scenario? This would explain the expiration after exactly 7 days. As per Google’s documentation:

Authorizations by a test user will expire seven days from the time of consent. If your OAuth client requests an offline access type and receives a refresh token, that token will also expire.

1 Like

Yes. Thanks! I will look into this further!