Updating Google Sheets and Awesome tables with Access queries

Each week I need to update charts and tables on a Google site using data that I have processed locally in MS Access.
         In the absence of being able currently to connect my local MS Access database direct to Google via JDBC or similar, I have found a method of updating Google Sheets and related Awesome tables with Access data, which uses a combination of Drive, Google Apps Script and a Google Sheet.

The idea of this is that the new exports from Access are saved to a specific drive folder each week then a script is run from a master Google sheet which looks for these specific file names and finds the new keys, With these keys identified, the sheet then can use a standard IMPORTRANGE function to bring in the new applicable data.  This then automatically updates Awesome tables and charts on a Google site linked to that master Google sheet.

To start, I export all the relevant Access queries to my local drive, and then uploaded and converted these to Google sheets format to a specific Drive folder which is accessible by anyone with the link. (I also delete the old files that these are intended to replace)

On my master Google sheet, I have a sheet called "FindKeys" - The script looks for a specific drive folder (e.g. abcdefghijk12345678 in the below) and then lists the file names and the spreadsheet keys for each item in the sheet

function search()  {


    var folder =  DriveApp.getFolderById("abcdefghijk12345678");
    
  var files = folder.getFiles();
  
   
     var sheetName = 'FindKeys';
     
var SheetIds = 2;

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  sheet.clear();

  sheet.appendRow(["FileName","SpreadsheetKey"]);

 while (files.hasNext()) {

   var file = files.next();
   Logger.log(file);
 var data;
    data = [
     file.getName(),
  file.getId(),
    ];

    sheet.appendRow(data);

  }
};

Since I now have the spreadsheet keys, I can use a simple Vlookup formula on another sheet, to return the keys for only the specific queries that I am interested in.

Since the cell reference of each key that is found will not change, these can then be used in another sheet to bring in the data using IMPORTRANGE.   In the example below, I have this formula on row 3, which allows me to use rows 1 and 2 in the right format for this data to be easily used in an Awesome Table

My master sheet contains a number of these IMPORTRANGE queries  - this allows me to update all the tables at once from this master sheet.  I just need to open the master sheet, run the script to identify the current keys, and after 30 seconds or so, all the reports will update to the latest data based from the current Access queries and show in an Awesome table on my Google site.

This process could be made even more automated by running a script with a timer, to rebuild the Importrange formulae on a regular basis - for my purposes as this is a weekly job, I am happy to keep the manual step of opening up the master sheet each week to do the updates

Hope this is of use - any questions or suggestions welcomed