Fetch BigQuery data to Google Sheets using Google Apps Script

Welcome!

Google Apps Script provides a powerful way to automate workflows and interact with various Google services, including BigQuery. This article walks through an end-to-end implementation of querying BigQuery from Google Sheets using Apps Script, explaining each function in detail.

The goal is to create a button-driven interface in Google Sheets that would let users fetch data instantly, filtering by data by specific queries with just a single click.

So let’s get started.

If you want a video version of the blog, check out the YouTube Tutorial given below.

Sample Google Sheet

The Google Sheet that I will be using for this tutorial is a log of product user experience and content tests and the goal of the client is to fetch data based on the ID’s of the tests. The Google Sheet contains the following details:

1. ID
2. Test Name
3. Participants
4. Test Focus
5. Last Fetched

The goal is to fetch data from BigQuery based on certain preset queries/conditions easily into Google Sheets. To achieve this, we will be using the BigQuery API and Google Apps Script.

Sample BigQuery Dataset

For this tutorial, I will be using a dataset containing logs of product user experience and content testing. The dataset is stored in BigQuery, and I would like to fetch this data into Google Sheets.

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:

1. Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor, let’s code.

Note: This article demonstrates how to fetch and send data to BigQuery using Google Apps Script with Service Accounts and OAuth authentication. We are not covering the setup of Service Accounts in this guide, but a dedicated article on that topic will be published soon.

Fetching data from Big Query

A core function of the script is executing a query in BigQuery and retrieving relevant results. The scripts follows these steps:

1. Reads test IDs from the Google Sheet.
2. Constructs and Executes a BigQuery query.
3. Processes the query results.
4. Updates the Google Sheet with the fetched data.

Prerequisites

Before you begin, ensure you have:

• Access to BigQuery and Google Cloud Console
• A Google Cloud Project with BigQuery API enabled
• A dataset and table in BigQuery
• A Google Sheet where you want to display the results

var CREDENTIALS = {
  private_key: "your_private_key"
  client_email: 'examplename@project2-283514.iam.gserviceaccount.com',
};

To securely connect to BigQuery, we use a service account for authentication. The credentials can be stored in Scripts Properties or Google Secret Manager to avoice hardcoding them directly.

The ion getOAuthService(user) handles authentication by using the OAuth2 service. The rest() function resets the authentication service, useful when credentials need to be refreshed.

Check out the link below to learn more about the OAuth2 Library.

GitHub - googleworkspace/apps-script-oauth2: An OAuth2 library for Google Apps Script.

function executeQuery(query) {
  const service = getOAuthService(CREDENTIALS.client_email);
  if (!service.hasAccess()) {
    throw new Error('Authorization required. Check script permissions.');
  }

  const request = {
    query: query,
    useLegacySql: false
  };
  const projectId = 'project2-283514';
  return BigQuery.Jobs.query(request, projectId);
}

The executeQuery(query) function runs a SQL query in BigQuery using the Apps Script BigQuery API. We start of by verifying oAuth access before executing the query.

The query request body is constructed followed by running the query within the specified project ID. The function then returns the query results.

// Function to process and log results
function processQueryResults(queryResults, sheet, headers, startRow) {
  if (queryResults.rows && queryResults.rows.length > 0) {
    const data = queryResults.rows.map(row => row.f.map(cell => cell.v));
    const lastRow = sheet.getLastRow();

    // Clear previous data
    if (lastRow > startRow - 1) {
      sheet.getRange(startRow, 2, lastRow - startRow + 1, headers.length).clear();
    }

    // Write headers and data
    sheet.getRange(1, 2, 1, headers.length).setValues([headers]);
    sheet.getRange(startRow, 2, data.length, headers.length).setValues(data);
  } else {
    Logger.log('No data found.');
  }
}

The processQueryResults() function parses and writes the retrieved data into the Google Sheet. The function begins by extracting row values from the query result, ensuring that relevant data is retrieved for processing.

Before we insert the new values, we perform a sheet cleansing steps, clearing any old data to prevent duplication. We set the column headers providing structure, followed by inserting the extracted query results into the appropriate row.

Incase no data is retrieved, a log message is generated to indicate the absence of new entries.

function FetchResults() {
  const spreadsheetId = 'your_spreadsheet_id';
  const sheetName = 'your_sheet_name';
  const timestampCell = 'your_timestamp_cell';

  const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  const sheet = spreadsheet.getSheetByName(sheetName);
  const ids = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues().flat();

  if (ids.length === 0) {
    Logger.log('No IDs found in the sheet.');
    return;
  }
  // This is an example query you can always customise it to your needs.
  const query = `
    SELECT 
      \`Test Name\`, 
      \`Participants\`, 
      \`Test Focus\`
    FROM \`project2-283514.invoices_dataset.FInalTry\`
    WHERE \`id\` IN (${ids.map(id => `'${id}'`).join(", ")})
    ORDER BY \`Test Name\`;
  `;

  const queryResults = executeQuery(query);
  processQueryResults(queryResults, sheet, ['Test Name', 'Participants', 'Test Focus'], 2);
  sheet.getRange(timestampCell).setValue(`Last Updated: ${new Date()}`);
}

The FetchResults() is the main function that retrieved the query results for a list of IDs stored in the Google Sheet. We begin by fetching the Google Sheet and the required IDs.

Using the IDs, we create a dynamic SQL query to fetch relevant data. The retrieved data is then passed processQueryResults() , which formats and displays the results appropriately. Once the data is inserted, a timestamp cell is updated to record the last data refresh.

Our code is complete and good to go.

Check the Output

Its time to see if the code is able to retrieve query results for the list of IDs stored in the Google Sheet.

You can either run the code directly from the editor or use custom menus. Check out the tutorial give below to know more about different ways to execute your Google Apps Script code.

On running the code you should get an output like this in the Execution Log.

If you go back to the Google Sheet, you will see on successful execution, the script fetches the relevant data for the IDs and inserts it into the Google Sheet. Additionally, you can see the timestamp cell being updated every time the script is executed.

Now that you’ve learned how to fetch data from BigQuery into Google Sheets, you can apply this knowledge to your own data management needs. Start by setting up your BigQuery project and service account. Then, adapt the script to your specific data requirements. Experiment with different queries to meet your needs, unlocking powerful data analysis in Google Sheets.

Conclusion

By automating the data retrieval process with Google Apps Script, you can save significant time and effort previously spent on manual data entry and query execution. This streamlined approach empowers analysts to focus on data analysis and reporting instead of tedious tasks.