Google Sheets =IMPORTRANGE permissions

Describe the issue/error/question

I have built a workflow to Create a new spreadsheet, add a new sheet, insert the headers and is working fine.

I now want to insert data from another Spreadsheet using the IMPORTRANGE(spreadsheet_url; range_string) function. I can update the cells with the completed function but it requires permissions manually set. Right now I open the new spreadsheet and press the button and it works.

image

Is there a way to set the permissions to another sheet from a new sheet? All the sheets are using the same gmail account. Currently I am using Oauth2 Authentication. Maybe it needs a Service Account and add the same service account to all the sheets I want to reference?

What is the error message (if any)?

Please share the workflow

_({
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -370,
        -70
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "resource": "spreadsheet",
        "title": "={{$node[\"Spreadsheet Name\"].json[\"spreadsheetname\"]}}",
        "options": {}
      },
      "name": "Create Spreadsheet",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        30,
        -70
      ],
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "spreadsheetname",
              "value": "Mark Hanson Deal 25255122100"
            }
          ]
        },
        "options": {}
      },
      "name": "Spreadsheet Name",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        -170,
        -70
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "create",
        "sheetId": "={{$node[\"Create Spreadsheet\"].json[\"spreadsheetId\"]}}",
        "options": {
          "tabColor": "#0062FF",
          "title": "Request"
        }
      },
      "name": "Create Sheet Request",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        230,
        -70
      ],
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      data: [ $json.rows.split(',') ]\n    }\n  }\n];\n"
      },
      "name": "Convert",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        630,
        -70
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "update",
        "sheetId": "={{$node[\"Create Spreadsheet\"].json[\"spreadsheetId\"]}}",
        "range": "Request!A:Z",
        "rawData": true,
        "options": {}
      },
      "name": "Google Sheets16",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        820,
        -70
      ],
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "rows",
              "value": "key,created,referrer,type,privacy,motive,stage,borrowers,needs"
            }
          ]
        },
        "options": {}
      },
      "name": "Define header - REQUEST",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        420,
        -70
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Spreadsheet Name",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Spreadsheet": {
      "main": [
        [
          {
            "node": "Create Sheet Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spreadsheet Name": {
      "main": [
        [
          {
            "node": "Create Spreadsheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Sheet Request": {
      "main": [
        [
          {
            "node": "Define header - REQUEST",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert": {
      "main": [
        [
          {
            "node": "Google Sheets16",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Define header - REQUEST": {
      "main": [
        [
          {
            "node": "Convert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
})_

Share the output returned by the last node

Information on your n8n setup

  • n8n version:
  • 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, welcome to the community :tada:

I’m sorry to hear you’re running into trouble here. From quickly looking into this I needed to manually approve using the IMPORTRANGE function even when leaving n8n out of the equation completely. Their documentation also says “Spreadsheets must be explicitly granted permission to pull data”:

https://support.google.com/docs/answer/3093340?hl=en

So I don’t think there would be a way to make this work through n8n, regardless of the authentication type unfortunately.

If your n8n instance has access to both sheets, you could instead consider reading the data from the first sheet and then inserting it on the second sheet through n8n instead of using this formula.

Thank you for the answer! :+1: