I am trying to connect to my database in
I am trying to connect to my database in a google apps script so i can import and regularly update my tables in google sheets. I cannot figure out out to set up a connection that works. I tried setting up a looker data source using the instructions provided in other posts too but that would not work either. Please help
6 Replies
stormy-gold•6d ago
Hey! Was it this piece of documentation that you were following for setting up Neon as a data source in Looker?
Neon
Connect Looker Studio to Neon - Neon Docs
Looker Studio is Google's data visualization and business intelligence platform. This guide explains how to connect your Neon Postgres database to Looker Studio using a PostgreSQL data source. Get you...
absent-sapphire•6d ago
yea, i must have typed something in wrong earlier because I got it to work now.
The other part of my question was connecting to my database using a google app script to send my table or tables to a google sheet. I'm trying to set up a script that i can schedule to update my google sheet with data in my database. Is this possible to do? I couldn't find anything in this server where someone tried something similar
No matter what i tried i got the following error in my app script
The error is: Connection URL uses an unsupported JDBC protocol.
stormy-gold•6d ago
Yeah unfortunately Google Apps Script’s JDBC service doesn’t support Postgres. You could try Neon's Data API which might circumvent that
Neon
Getting started with Neon Data API - Neon Docs
Neon Auth Building a note taking app Neon Data API demo note taking app The Neon Data API, powered by PostgREST, offers a ready to use REST API for your Neon database. You can interact with any table,...
absent-sapphire•6d ago
Thank you, I'll try this
stormy-gold•6d ago
Let me know if it works! It would be useful so we can make an internal note for future users who face the same issue, and add some documentation around it.
absent-sapphire•6d ago
I was able to get it to work! Thanks for sending this over. I used Gemini to help me get it set up. Gemini wanted to take me down the path of using a api_key, project_id, and branch_id so i had to explicitly tell it i was simply using an API_url. I did need to follow the steps in the provided document to make sure the anonymous role had the correct privileges as well. I have a function in my query now that is able to reference multiple tables/views in my database and maps the data to corresponding sheets in my workbook. The method and code that i am using makes it necessary to have the table display the data how i want or create a specific view with the data formatted how its desired but i'm sure there is a way to map queries as well to bring in the data if someone doesn't want to create views for all the sheets they are mapping data to.
I currently have tableau public workbooks connected to google sheet source files because Tableau Public allows for automated and forced updates when connected to Google Sheets. However, i also have Shiny R forms that read and write data from the Google Sheets and they were getting extremely slow because of the size my Sheets were getting to because of numerous large formulas. This will be a great improvement for me to be able to speed up my Shiny R Forms while still allowing my Tableau workbooks to have automated updates