How to query sqlite file from Google Apps Script?

Question

I have a sqlite3 file in my google drive and I would like to setup a Google Apps Script from a spreadsheet in Google Drive to read that SQLite file database and fill some rows. Any idea on how to achieve this?

EDIT:

I managed to query the SQLite file using an additional html page with an async javascript call.

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Integrations')
      .addItem('SQLite','sqlite')
      .addToUi();
}

function sqlite() {
  const ui = SpreadsheetApp.getUi();
  const html = HtmlService.createHtmlOutputFromFile('sqlite').setTitle('SQLite');
  ui.showModalDialog(html, ' ');
}

function getDriveFile() {
    return DriveApp.getFilesByName('filename.db').next().getBlob().getBytes();
}

<html>  
  <head>
    <script src="https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/sql-wasm.min.js"></script>
    <script>
      // Async call to the DriveApp that loads the SQLite file
      let SQL, db, file;
      (async() => {
        SQL = await initSqlJs({ locateFile: file => 'https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/' + file });
        google.script.run.withSuccessHandler(buffer => {
          db = new SQL.Database(new Uint8Array(buffer));
          const stmt = db.prepare("SELECT * FROM TABLE WHERE ID=:id");
          const result = stmt.getAsObject({':id' : 1});
          console.log(result);
          //how can I pass the result back to the main gs file?
          google.script.host.close() 
        }).getDriveFile();
      })();
    </script>
    </head>
  <body>
      ...
  </body>
</html>

Try [JDBC](https://developers.google.com/apps-script/guides/jdbc) — Cooper, Dec 27 '22 at 20:37
Maybe this will help https://stackoverflow.com/questions/46924688/sql-like-query-features-in-google-app-script-to-pull-data-from-google-sheets — Cooper, Dec 27 '22 at 21:11
Thanks for the interesting link, anyway I have to query a SQLite file, not a spreadsheet. — user2013861, Dec 27 '22 at 21:17
There are no native solutions in GAS that allow you to read an sqlite3 file from Google Drive. If you're a highly competent programmer you can try writing your own sqlite3 engine/parser in GAS. If you're not at that level, you can try looking for a Javascript library or API for Sqlite and attempt to integrate it with Google Apps Script. It's probably doable, but I suspect it won't be practical or performant given the limitations of GAS as a platform. Still, if you manage to pull it off be sure to update us with your findings. — TheAddonDepot, Dec 27 '22 at 21:28
The answer is no. You cannot access the `.db` files via apps script the way you want it (or you want to program it from scratch.. if it is possible at all). You will need google cloud functions at least to get this done. — RemcoE33, Dec 27 '22 at 21:29
I don't have the skills for that. Anyway I managed to query the SQLite file from an html page in GAS that makes a javascript async call to a Drive function that retrieves the file. How can I get the query result back to the main gs script? — user2013861, Dec 27 '22 at 21:42

user2013861 · Answer 1 · 2022-12-28T21:45:28.310

Ok this way is working, not sure is the best way but will be enough for my little app. Thanks everyone for the inputs.

let jsResult;

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Integrations')
      .addItem('SQLite','sqlite')
      .addToUi();
}

function sqlite() {
  const ui = SpreadsheetApp.getUi();
  const html = HtmlService.createHtmlOutputFromFile('sqlite').setTitle('SQLite');
  ui.showModalDialog(html, ' ');
}

function getDriveFile() {
  return DriveApp.getFilesByName('filename.db').next().getBlob().getBytes();
}

function passResult(result) {
  jsResult = result;
  Browser.msgBox(result);
}

<html>  
  <head>
    <script src="https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/sql-wasm.min.js"></script>
    <script>
      // Async call to the DriveApp that loads the SQLite file
      let SQL, db, file;
      (async() => {
        SQL = await initSqlJs({ locateFile: file => 'https://cdn.jsdelivr.net/npm/sql.js@1.8.0/dist/' + file });
        google.script.run.withSuccessHandler(buffer => {
          db = new SQL.Database(new Uint8Array(buffer));
          const stmt = db.prepare("SELECT * FROM TABLE WHERE ID=:id");
          const result = stmt.getAsObject({':id' : 1});
          google.script.run.withSuccessHandler(google.script.host.close)
                           .passResult(result);
        }).getDriveFile();
      })();
    </script>
    </head>
  <body>
      ...
  </body>
</html>

How to query sqlite file from Google Apps Script?

1 Answers1