Bigquery data to populate Google Sheets automatically - having some issues, questions

blog
troubleshooting

#1

Hello,

For our data project, we are trying to have our scheduled Bigquery run data to populate into Google Sheets automatically at a certain time in the morning. The post I found on your site was very helpful and I was able to follow the example but for our actual data, I am not sure how to update or move on to the next step as that’s not explicitly written in the post.

What I mean by that is, I checked to see the Mother Age tab was successfully created but for our data, do I overwrite in the same cells? Or, do I go onto the next row in the ‘Copy of Bigqueries Script’ file to set up our trigger?

If it’s easier, I am also happy to quickly ask via phone. Please let me know.

Thank you!
Irene Lee


#2

Hi @ileemvc glad to hear you find that useful! So, you have followed the entire procedure outlined in the post, especially Step 3: Automate the data transfer?

If not, can you please tell me exactly at which stage of the instructions you’re struggling?

Thanks!


#3

Hi Bobby,

I did up to step 4 of the first part. My question is these steps are for the demo query so when I run my own query, I can just overwrite in the demo query cell in the Copy of BigQuery Script Google Sheet, correct?

Also, I am running into a problem, where I successfully created the Trigger using the time-driven format, but it doesn’t seem to auto-populate in the connected Google Sheet. It only performs the Trigger action AFTER I click on the ‘Sheet Go --> Run Query’ in the menu bar. Is this the only way? Is there no way to automate this?

Thanks!
Irene


#4

Correct!

Have you enabled error emails?


#5

I have, but I didn’t get anything.


#6

So I got an error message but it’s weird because I can run the exact queries in Bigquery as the coding is valid. Why am I getting this result?


#7

@ileemvc can you share the query here that you are using?


#8

Is there a way to send not via public message for my queries as it includes some company-related data?


#9

I’ts just simple lines of queries - as shown below. Sharing all the images of error message and my script sheet - that seems to run when I hit sheet go but gives me an error email when I do the trigger.

SELECT *
FROM mvc_leads.scheduled_callcenter_total_leads_combined_zapier_FINAL


#10

This is the google sheet where you insert codes.


#11

@ileemvc this is indeed weird that it works manually but not on trigger.

Can you temporarily share your sheet with my email robert@sheetgo.com, for me to take a closer look?

Thank you!


#12

Yes, sending you the email shortly. Thanks so much for looking into this! Please get back to me as soon as possible if you can. Appreciate all the help!


#13

@ileemvc have you ensured you’re actually running the right function on trigger? It needs to be run_query:

image


#14

Yes, I definitely selected that option


#15

I think I know what the problem is. When you run the trigger in the background, the spreadsheet is not open. Therefore var query_data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings").getRange("2:2").getValues(); doesn’t work.

Try to add SpreadsheetApp.openById("1A0bsyVHDAYiWDnI7m6fFTC37nRxIfUY9FSnJdQ5gNKk"); as shown below:

Does that work?


#16

Okay, updated with one line added with the code you mentioned. Thank you! Here’s the screenshot. Let me see if this works.


#17

I think it’s working now! Thanks so much for your help!
One question I have is is it okay to change the sheet name and all?
Also, do you see form the back-end scripts or from my current sheet any issues that might come up later? I ask this because it is crucial that we get this updated every day at a certain time. Please advise.


#18

So glad to hear! :slight_smile:

Yes, the ID wouldn’t change.

No, I don’t see any remaining issues. You are safe to go.

Good luck!


#19

Hi I need help, i am receiving this error:

API call to bigquery.jobs.query failed with error: Invalid table name: `table_name.events_*` [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)]. (line 18, file "Code")

How do I resolve this. Thank you!


#20

@Clark_Llorador can you share your entire query statement, please?