We’ll introduce sample code to search like SQL using Google Spreadsheet + Google Apps Script (GAS) + QUERY function.

Background: Want to Treat Spreadsheet Like a Database

We wrote sample code to see if we could achieve SQL-like functionality using the QUERY function to treat Google Spreadsheet like a database from Google Apps Script (GAS).

Prerequisites: Spreadsheet users Sheet

id	email	updated_at
1	[email protected]	2022-01-01 1:01:01
2	[email protected]	2022-02-02 2:02:02
3	[email protected]	2022-03-03 3:03:03

Sample Code: Spreadsheet + Google Apps Script (GAS) + QUERY Function

The sample code for Google Apps Script (GAS) + QUERY function to retrieve only rows where updated_at is newer than a specific datetime from the users spreadsheet is as follows:

function searchUsers(){
  let keyword = '2022-01-01 01:01:01';
 
  const sheet = SpreadsheetApp.getActive().getSheetByName("QUERY");
  sheet.getRange(1, 1).setValue(`=QUERY(users!A:C,"where C > datetime '${keyword}'", -1)`);

  const lastRow = sheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    let id = sheet.getRange(i, 1).getValue();
    let email = sheet.getRange(i, 2).getValue();
    let updatedAt = sheet.getRange(i, 3).getValue();
     
    Logger.log(`id: ${id}, email: ${email}, updated_at: ${updatedAt}`);
  }
}

Above, we searched like SQL using Google Spreadsheet + Google Apps Script (GAS) + QUERY function.

That’s all from the Gemba.

Reference Information

• Google Visualization API のクエリ言語
• Query Language Reference (Version 0.7) | Charts | Google Developers