如何从谷歌应用程序脚本查询sqlite文件？

v7pvogib 于 2022-12-29 发布在 SQLite

关注(0)|答案(1)|浏览(169)

我有一个sqlite3文件在我的谷歌驱动器，我想从谷歌驱动器中的电子表格设置一个谷歌应用程序脚本来读取该SQLite文件数据库和填充一些行.任何想法如何实现这一点？
编辑：
我设法使用一个附加的html页面和一个异步javascript调用来查询SQLite文件。

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>

sqlite

来源：https://stackoverflow.com/questions/74933620/how-to-query-sqlite-file-from-google-apps-script

1条答案

按热度按时间

8gsdolmq1#

好的，这种方式是工作，不确定是最好的方式，但将是足够的我的小应用程序。感谢大家的投入。

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>

赞(0）回复(0）举报 2022-12-29

我来回答

如何从谷歌应用程序脚本查询sqlite文件？

1条答案

相关问题

热门标签

最新问答