Hey guys, I used google apps script to figure out how to import your entire Glide Big Table.
Here are the two functions I used to make this happen:
//Variable to store all rows outside the row reader function
var allRows = [];
//This function can read and return all the rows from any Glide Table
/**
* Can read all the rows in any one glide table.
* @param {String} app_id The designated ID to define each app built on Glide
* @param {String} secret_code The 'Bearer' token provided by Glide allowing you to access their API
* @param {String} table_name The name of the table assigned by Glide storing the subset of rows
* @param {String} continuation The continuation token provided by a previous API call to help retrieve the next batch of data
* @return {Object} Returns an array object full of json values, each json object will be a row in the table
* @customfunction
*/
function Read_Rows_From_Any_Glide_Table(app_id,secret_code,table_name,continuation){
console.log('Commenced '+arguments.callee.name); //This will display the start of the function in the console
//Trying to catch errors
try
{
//Defining the API endpoint URL
var url = 'https://api.glideapp.io/api/function/queryTables';
//Defining the headers so that Glide's server can authorize your API call
var headers =
{
'Content-Type': 'application/json', //Content type will always be application JSON
'Authorization': 'Bearer '+secret_code, //The secret API provided to each unique user by Glide
};
//Defining the data payload to tell Glide's server where and how to query the data from
var data =
{
appID: app_id, //The authorised app ID provided by Glide within which the table is used
queries:
[
{
tableName: table_name, //The name of the table assigned by Glide storing the subset of rows
startAt: continuation, //The continuation token provided by a previous API call to help retrieve the next batch of data
}
],
};
//Defining the options to tune the API call to be read by Glide's server
var options =
{
method: 'post',// All requests to Glide's servers are 'POST' request
headers: headers,// As defined above
payload: JSON.stringify(data),
muteHttpExceptions: true // Set as 'TRUE' to examine the full server response
};
var response = UrlFetchApp.fetch(url, options);
var response_parsed = JSON.parse(response.getContentText());
//Pushing the rows from the first API call to the 'all_Rows' variable defined outside this function
allRows = allRows.concat(response_parsed[0]['rows']);
//Blocking the next console log command to generate a simpler log
//console.log({'output':response_parsed,'row data length':response_parsed[0]['rows'].length, 'total data length': allRows.length});
//Checking to see if a 'next' json has been provided
if(response_parsed[0]['next'])
{
//Pass the 'next' value as is, without parsing
console.log("Partial data fetched. Another call will be made."); //If there is a 'next', return more rows
console.log('Terminated '+arguments.callee.name); //This will display the end of the function in the console
Read_Rows_From_Any_Glide_Table(app_id,secret_code,table_name,response_parsed[0]['next']);
console.log('Terminated '+arguments.callee.name); //This will display the end of the function in the console
return {'status':'success','payload':allRows,'error':null}
}
else
{
console.log("All data fetched"); //If there is no 'next', return all rows
console.log('Terminated '+arguments.callee.name); //This will display the end of the function in the console
return {'status':'success','payload':allRows,'error':null}
}
}
catch(error)
{
console.log('Failed to run the function due to '+error);
return {'status':'failure','payload':null,'error':error};
}
}
//This function will parse data from a json format and return it in a format that will allow google sheets to read it
/**
* Can transform a JSON dataset into a format readable by google sheets.
* @param {Array} json_object_array The JSON dataset array provided from another function
* @return {Object} Returns an array of all the json values, in a google sheets readable format
* @customfunction
*/
function Parse_JSON_Dataset_To_Google_Sheets(json_object_array){
console.log('Commencing '+arguments.callee.name); //This will display the start of the function in the console
//Trying to catch errors
try
{
//Retrieving the firstg json object to get the json keys
var object_0 = json_object_array[0];
//The keys are being obtained next to allow the mapping of all row values
var object_0_keys = Object.keys(object_0);
//Mapping each through all the rows to then map each row item by its JSON Key
var mapper_1 = json_object_array.map(json_object =>
{
var row_array = []; //Creating a row array for the looper below to add elements for each cell in the row
//Looping through each key-value pair in the JSON object using the JSON keys
var looper_1 = object_0_keys.forEach(key =>
{
//If the json array does not contain that key value pair, a blank value will be inserted
var cell_item = json_object[key] || '';
row_array.push(cell_item);
});
//Adding the headers to the row array
row_array.unshift(object_0_keys);
return row_array;
});
//Returning the enitre mapped array
console.log('Terminating '+arguments.callee.name); //This will display the end of the function in the console
return mapper_1;
}
catch(error)
{
console.log('Failed to run this function due to '+error);
console.log('Terminating '+arguments.callee.name); //This will display the end of the function in the console
return null;
}
}
function Test_1(){
var glide_table_data_cities_towns = Read_Rows_From_Any_Glide_Table('YOUR_APP_ID','GLIDE_SECRET_CODE','GLIDE_TABLE_NAME');
var glide_table_data_cities_towns_payload = glide_table_data_cities_towns['payload'];
Parse_JSON_Dataset_To_Google_Sheets(glide_table_data_cities_towns_payload);
}
The last function, called ‘Test_1()’ is just a dummy function used to test the working version of the Glide Big Tables API call!
@Darren_Murphy
@Sean_Martin
@Robert_Petitto
@DarrenHumphries
@experts
Please review this when you guys have the time!
My use case for this would be as such:
-
Using the data for analytics.
-
Using this data to push to a normal glide table to avoid incurring the query costs.
-
Using this data to create basic automations based on the analysed data to push back to Glide. (Currently will not be possible until Glide releases the Automations feature).