From 5549d2fde135105de7dcd9b9e7df84c217f4cf77 Mon Sep 17 00:00:00 2001 From: Ardis Lu Date: Sat, 27 Nov 2021 20:04:37 -0800 Subject: [PATCH] Generate a sqlite database instead of a query Implemented by using client-side code which calls sql.js --- Code.gs | 12 ++++++++---- query-to-db.html | 34 ++++++++++++++++++++++++++++++++++ 2 files changed, 42 insertions(+), 4 deletions(-) create mode 100644 query-to-db.html diff --git a/Code.gs b/Code.gs index 69ce57e..2417422 100644 --- a/Code.gs +++ b/Code.gs @@ -9,7 +9,7 @@ const BACKBLAZE_KEY = PROPERTIES.getProperty('BACKBLAZE_KEY'); function onOpen() { UI.createMenu('SQLite') .addItem('Help', 'showHelp') - .addItem('Generate SQLite database query', 'generateDatabaseQuery') + .addItem('Download SQLite database', 'showDatabaseDownloadDialog') .addItem('Upload to Backblaze', 'uploadToBackblaze') .addToUi(); } @@ -19,7 +19,7 @@ function showHelp() { USAGE: - Edit the data on this workbook like you would on any other Google Sheet, noting KEY ASSUMPTIONS below. -- When ready, press the "Generate SQLite database query" button to generate a SQL query which can be used to write a SQLite database containing this workbook's data! +- When ready, press the "Download SQLite database" button to generate and download a SQLite database from the workbook's data! KEY ASSUMPTIONS: - Each tab in this workbook is a data table @@ -32,6 +32,7 @@ KEY ASSUMPTIONS: UI.alert('Help', helpMessage, UI.ButtonSet.OK); } +// Helper function which will be called from the client-side dialog function generateDatabaseQuery() { const sheets = WORKBOOK.getSheets(); @@ -92,9 +93,12 @@ function generateDatabaseQuery() { `; } - DriveApp.getFileById(FILE_ID).setContent(query); + return query; +} - UI.alert('Success!', `Query generated! Download here: https://drive.google.com/u/0/uc?id=${FILE_ID}&export=download`, UI.ButtonSet.OK); +function showDatabaseDownloadDialog() { + const html = HtmlService.createHtmlOutputFromFile('query-to-db'); + UI.showModalDialog(html, 'Generating database...'); } function uploadToBackblaze() { diff --git a/query-to-db.html b/query-to-db.html new file mode 100644 index 0000000..c016013 --- /dev/null +++ b/query-to-db.html @@ -0,0 +1,34 @@ + + + + + + + + + Generating database... + + + + + + \ No newline at end of file -- 2.47.3