Image credit: Ben Collins (benlcollins.com)

Master the QUERY function, the most powerful function in Google Sheets, to become a more effective data analyst

Friend of Pulse and Google Sheets guru, Ben Collins, has a new course ‘The QUERY Function in Google Sheets’. The QUERY function it lets you perform various data manipulations making it easy to reshape, aggregate and explore your data in Google Sheets. The course is designed to be suitable for everyone from beginner to advanced who are interested in ways to work more effectively with your data.

If you are not familiar with the QUERY function Ben provides one example of what is possible in Sheets Tip 204: How To Use Dates In The QUERY Function (check the linked post in this Sheet Tip for an example worksheet).

Bonus: Sheets Tip 204 includes a 50% discount on the course valid until Friday 20 May 2022 at midnight EDT.

Finally, if you are interested in using the QUERY language in Google Apps Script it is possible! Below is some code used in this copy of Ben’s example workbook based on:

// based on https://gist.github.com/tanaikech/053d3ebbe76fa7c0b5e80ea9d6396011#sample-script

function myFunction() {
  const doc = SpreadsheetApp.getActive()
  const spreadsheetId = doc.getId(); // or set another Spreadsheet ID.
  const sheetId = doc.getSheetByName('Data').getSheetId(); // or set another Sheet ID from Spreadsheet ID.

  const query = "select C, B where B > date '2000-01-01' and B <= date '2002-12-31'"; // your QUERY

  const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?tqx=out:csv&gid=${sheetId}&tq=${encodeURI(query)}&access_token=${ScriptApp.getOAuthToken()}`;

  const res = UrlFetchApp.fetch(url,);
  console.log(res.getContentText());

  const array = Utilities.parseCsv(res.getContentText());
  console.log(array);

  // SpreadsheetApp.getActiveSpreadsheet(); // This comment line is put for automatically detecting the scopes if directly adding a spreadsheet ID.
}

Source: The QUERY Function in Google Sheets

Martin Hawksey

Member of Google Developers Experts Program for Google Workspace (Google Apps Script) and interested in supporting Google Workspace Devs.

mashe.hawksey.info