Automate a daily data collection on BigQuery

Google Cloud Data Analytics
3

Hi @Salade here are a couple of super simple ways to automate your daily user report from BigQuery to Google Sheets:

Option 1: Use Connected Sheets

This is the easiest no-code way:

  1. Open a Google Sheet.

  2. Go to Extensions > Connected Sheets > Connect to BigQuery.

  3. Select your project, dataset, and the table or view you want.

  4. Use the “Schedule refresh” option from the Connected Sheets panel (top right corner).

You can base it on a simple aggregated query like:

SELECT DATE(event_timestamp) AS date,
COUNT(DISTINCT user_id) AS daily_users
FROM your_project.your_dataset.your_table
GROUP BY date
ORDER BY date DESC

Option 2: Use Apps Script (more flexible)

If you want a bit more control:

  1. Open a Google Sheet.

  2. Go to Extensions > Apps Script.

  3. Paste a script like this:

function getUsersFromBigQuery() {
const projectId = ‘your-project-id’;
const query = SELECT DATE(event_timestamp) AS date, COUNT(DISTINCT user_id) AS daily_users FROM \your_project.your_dataset.your_table`
GROUP BY date
ORDER BY date
DESC LIMIT 30`;

const request = {
query: query,
useLegacySql: false
};

const queryResults = BigQuery.Jobs.query(request, projectId);
const rows = queryResults.rows;

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Report”) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(“Report”); sheet.clear();
sheet.appendRow([“Date”, “Unique Users”]);

for (let i = 0; i < rows.length; i++) {
sheet.appendRow([rows[i].f[0].v, rows[i].f[1].v]);
}
}

Then set up a daily trigger to run the script automatically.

Bonus: Want something more powerful?

If you’re looking for something more visual or that lets you combine data from different sources, you could try a platform like Windsor.ai. It connects both to BigQuery and Google Sheets and lets you build automated, no-code workflows.

Hope this saves you a bunch of hours , and earns you the title of “Productivity Lazy Genius”
Good luck!