I’m working with Google Sheets API in .NET and running into an issue with the IMPORTRANGE function. I’ve got two spreadsheets that I create through the API. The first sheet needs to pull data from the second one using IMPORTRANGE, but there’s always this permission popup that shows up asking me to click “ALLOW” to authorize the connection.
This is really annoying because I want everything to be automated. Is there some way to handle this authorization step programmatically? I need the sheets to connect without any manual intervention.
I’ve been digging through the Google Sheets API documentation and the Drive API docs but can’t seem to find a method that handles this specific scenario. Has anyone figured out how to bypass this manual approval step when using IMPORTRANGE between programmatically created sheets?
The trick is in the service account setup. I ran into this same issue and found that both spreadsheets need to be created with the same service account credentials. Google shows that auth popup because it treats each sheet as having different owners, even when you create them programmatically. What fixed it for me: make sure your .NET app uses one service account for everything - creating sheets, adding formulas, all of it. Also, set the IMPORTRANGE formula right after creating both sheets while you’re still in the same API session. This stops the permission dialog from popping up. Google’s permission system trusts operations from the same session when they’re using identical service account authentication.
I encountered a similar issue previously. The solution lies in utilizing the Drive API to share the source sheet with the owner of the destination sheet before using IMPORTRANGE. Specifically, you should employ the permissions.create method to grant read access. This procedure allows IMPORTRANGE to function seamlessly without any manual intervention. Ensure this step is performed right after the creation of the source sheet and before introducing the IMPORTRANGE formula, as Google handles IMPORTRANGE authorizations akin to standard sharing permissions.
yeah, importrange is such a pain sometimes. just ensure that both sheets are owned by the same account or share the source sheet with edit access to the destination sheet owner. if done via the same service account, should bypass the popup.