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




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


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?



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?




Have you enabled error emails?


I have, but I didn’t get anything.


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?


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


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


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.

FROM mvc_leads.scheduled_callcenter_total_leads_combined_zapier_FINAL


This is the google sheet where you insert codes.


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

Can you temporarily share your sheet with my email, for me to take a closer look?

Thank you!


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!


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



Yes, I definitely selected that option


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?


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


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.


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!


Hi I need help, i am receiving this error:

API call to failed with error: Invalid table name: `table_name.events_*` [Try using standard SQL (]. (line 18, file "Code")

How do I resolve this. Thank you!


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