Embedded Files
Algemeen
Algemeen
Hieronder zijn een aantal helperfuncties vervat die het gebruik van een object data structuur toelaten om bepaalde functies uit te voeren.
TableToObject(sheetname)
TableToObject(sheetname)
De functie vertaald een bestaande sheet in een spreadsheet naar een object.
De titelrij is de keyname, de waardes zijn telkens array's waarbij de index de row was waarop de waarde stond in de sheet.
Hierdoor wordt het mogelijk om nadien dmv de property (kolom titel) en indexOf een waarde op te zoeken in het object en de overeenkomstige waarde in een andere kolom te gebruiken.
/**
* Convert sheets to objects
* @param {string} "sheetname" sheet in active SS
* @return {object}
*
*
*/
function TableToObject(sheetname) {
// Get the table values
var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname).getDataRange().getValues();
// Extract headers
var headers = values[0];
// Extract the body
var body = values.slice(1);
// Initialize keys obj
var keys = {};
// No headers, create empty object
if(headers.length==1 && body.length==0 && headers[0]==""){
return keys
}
// You need an index for looping through the body rows
var i = 0;
// For each header
headers.forEach(function (c) {
// Create an empty list for the values
keys[c] = [];
// For every row in the body assign the values to their keys
body.map(r => keys[c].push(r[i]));
i++;
})
// Logger.log(keys["Call"]);
return keys;
}
SStoObj()
SStoObj()
Wrapper functie van voorgaande, waardoor je alle sheets binnen een spreadsheet in Ă©Ă©n keer kan omvormen tot een object met daarin objecten van iedere sheet.
/**
* Convert Spreadsheet to Object within Object
* @return {Object} list of object sheets within object
*/
// convert all sheets in spreadsheet to obj within an object SS.
function SStoObj() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var SS = {};
for (i = 0; i < sheets.length; i++) {
let name = sheets[i].getName();
//convert sheet to object, use name of sheet.
SS[name] = TableToObject(name);
}
return SS;
}
ImportToObject(ID, sheetname)
ImportToObject(ID, sheetname)
Deze functie laat je toe om uit een andere sheet de import te doen (importrange vervanger)
/**
* Convert imported sheets to objects
* @param {FileID} ID of file to import from
* @param {string} "sheetname" sheet in active SS
* @return {object}
*
*
*/
function ImportToObject(ID, sheetname) {
// Get the table values
var values = SpreadsheetApp.openById(ID).getSheetByName(sheetname).getDataRange().getValues();
// Extract headers
var headers = values[0];
// Extract the body
var body = values.slice(1);
// Initialize keys obj
var keys = {};
// You need an index for looping through the body rows
var i = 0;
// For each header
headers.forEach(function (c) {
// Create an empty list for the values
keys[c] = [];
// For every row in the body assign the values to their keys
body.map(r => keys[c].push(r[i]));
i++;
})
// Logger.log(keys["Call"]);
return keys;
}
ObjectToTable(obj, sheetname)
ObjectToTable(obj, sheetname)
Vertaalt een object waarbij keys / value paren die voor iedere element bestaan worden omgevormd naar een 2D array, waarbij de eerste rij, de namen van de keys zijn en de andere rijden de waardes van de elementen.
Nadien wordt dit terug geschreven naar de betreffende sheet.
/**
* Convert object to sheet (single array key/value)
* @param {object} "object"
* @param {sheet} "sheet"
* @return
*
*/
function ObjectToTable(obj, sheetname) {
//
let Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
let arr = [[]];
var keys = Object.keys(obj);
arr.push(keys);
arr.shift(); //haal eerste leeg array row weg owv het expliciet definieren van een 2d array.
var keyval = Object.values(obj);
// aantal elementen in Ă©Ă©n keyvalue array (was kolom) is het aantal rows dat je moet toevoegen
for (r = 0; r <= keyval[0].length; r++) {
let row = [];
//loop door alle kolommen (lengte -1)
for (c = 0; c <= keyval.length - 1; c++) {
row[c] = keyval[c][r];
}
arr.push(row);
}
Sheet.clear()
// plaats de array terug in de opgegeven sheeft vanaf A1
Sheet.getRange(1, 1, arr.length, arr[0].length).setValues(arr);
SpreadsheetApp.flush();
return
}
ConvertObjArr(arr)
ConvertObjArr(arr)
In heel wat API's krijg je als antwoord op een query een Array van objecten, met telkens dezelfde key=value paren (zoals een db query)
Deze functie vertaald een array van objecten, naar een object bestaande arrays waarbij iedere array wordt toegekend aan Ă©Ă©n key, naar analogie met de TableToObject() functie.
Hierdoor wordt het mogelijk om nadien dmv de property (kolom titel) en indexOf een waarde op te zoeken in het object en de overeenkomstige waarde in een andere kolom te gebruiken.
/**
* Convert to array of objects to object of arrays
* @param {Array} Array of objects for each user
* @returns {object} object
*
*/
function ConvertObjArr(arr) {
let nameobj = {}
if (arr.length > 0) {
//create object properties from first element in original object
var keys = Object.keys(arr[0])
//fill Array element of property
for (i = 0; i < keys.length; i++) {
//Create properies
if (!nameobj[keys[i]]) {
nameobj[keys[i]] = []
for (u = 0; u < arr.length; u++) {
nameobj[keys[i]][u] = arr[u][keys[i]];
}
}
}
} return nameobj
}
ArrayOfObjToTable(arrOfObj, sheetname)
ArrayOfObjToTable(arrOfObj, sheetname)
Wanneer je het antwoord van een Query, waarin je een Array krijgt van objecten met identieke keys voor alle elementen, in Ă©Ă©n stap wil schrijven naar een sheet (ter controle of als user interface), kan je gebruik maken van de volgende fuctie.
/**
* ArrayOfObjToTable
* Convert array of objects with identical keys to sheet (single array key/value)
* @param {array} "arrOfObj" array of objects with single key/value
* @param {sheet} "sheet"
* @return {array} "arr", array of values
*
*/
function ArrayOfObjToTable(arrOfObj, sheetname) {
//
let Sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
let arr = [[]];
if (arrOfObj.length > 0) {
var keys = Object.keys(arrOfObj[0]);
arr.push(keys);
arr.shift(); //remove first empty row, due to the explicite 2d array definition.
// loop through all objects in array
for (e = 0; e < arrOfObj.length; e++) {
var keyval = Object.values(arrOfObj[e]);
arr.push(keyval);
}
}
Sheet.clear()
// plaats de array terug in de opgegeven sheeft vanaf A1
Sheet.getRange(1, 1, arr.length, arr[0].length).setValues(arr);
SpreadsheetApp.flush();
return arr
}
Toepassingen
Toepassingen
Door de verstaalslag worden bepaalde functies stukken eenvoudiger.
Hier een voorbeeld waarbij je waardes vanuit twee "kolommen" samen voegd tot Ă©Ă©n kolom(in object)
function NameKey(SS) {
//Name_key for UBAEportBEARS
if (!SS.UBAExportBEARS.Name_key) { SS.UBAExportBEARS.Name_key = [] }
for (let key in SS.UBAExportBEARS.Call) {
SS.UBAExportBEARS.Name_key[key] = `${SS.UBAExportBEARS.First_Name[key]} ${SS.UBAExportBEARS.Second_Name[key]}`;
}
Wanneer je een waarde moet zoeken in een array kan je gebruik maken van de indexOf en lastIndexOf functies, die een index nummer terug geven.
Hierdoor kan je in verschillende kolommen/sheets op zoek gaan naar deze waardes.
In dit voorbeeld is de volledige spreadsheet omgevormd naar een object SS. waarin de subproperties telkens een sheet vormen (UserExport, UsersDataCompat, UBAExportBEARS) en in deze sheet de kolommen omgevormd zijn naar Object properties, bestaande uit een array(Name_key, Call, Employee ID..).
De uitkomst geeft ons de exacte locatie van de gevonden waarde in een tabel.
for (var idx in SS.UBAExportBEARS.Call) {
let UE = SS.UsersExport["Employee ID"].indexOf(SS.UBAExportBEARS.Call[idx]);
let UEN = SS.UsersExport.Name_key.indexOf(SS.UBAExportBEARS.Name_key[idx]);
let UDC = SS.UsersDataCompact.Call.lastIndexOf(SS.UBAExportBEARS.Call[idx]);
let UEO = SS.UBAExportOthers.Call.indexOf(SS.UBAExportBEARS.Call[idx]);
if (UE !== -1 && UDC !== -1) {
//found as existing B-EARS user and manually updated data.
SS.UBAExportBEARS.State_user[idx] = 2
We gaan voor alle Call in de sheet UBAExportBEARS, kijken of deze bestaat in de andere sheets zijnde UserExport, UserDataCompact, UBAExportOthers en tevens kijken we of eventueel de Name_key niet vookomt, wat er op zou wijzen dat de callsign is aangepast.
GetSheetFromSS(url, sheet, colarr, obj, objprop)
GetSheetFromSS(url, sheet, colarr, obj, objprop)
Deze functie is de vervanger van de ImportRange vanuit een andere spreadsheet.
De import gebeurt naar een object en properties (sub object)
Toepassing
Toepassing
Met de volgende code vervang je de sheet formules voor een importRange.
Hierbij wordt de data uit de spreadsheet met URL gehaald, van de sheet "Form Responses 1".
Deze data wordt in het object SS samen gebracht in het formaat waarbij de sheet wordt opgebouwd als een object van array's waarin de kolom headers de keys zijn en de array eronder de waardes bevatten.
Nadien kan dit object terug omgezet worden naar een echte sheet binnen de huidige spreadsheet, zoals hier naar UsersDataCompact.
Vermits we gebruik maken van een lege kolom array worden alle kolommen over gehaald.
let SS={}
//get UserDataCompact complete sheet
let UsersDataCompact = GetSheetFromSS("https://docs.google.com/spreadsheets/d/1CoQ...X4xgpsBH0tMMbiNA/edit#gid=1521130050","Form Responses 1",[],SS,"UsersDataCompact");
ObjectToTable(SS.UsersDataCompact,"UsersDataCompact")
Wil je een selectie van een aantal kolommen, dan dien je de headers ervan te definiëren in de colarr array.
//get Privacy Internal
let PrivacyInternal = GetSheetFromSS("https://docs.google.com/spreadsheets/d/1zvsblH8iPgTYHCYVmxSYmrRLL2wS_q93W2la6clcMt4/edit#gid=1417707330","Form responses 1",["Timestamp","Email address"],SS,"PrivacyInternal");
ObjectToTable(SS.PrivacyInternal,"PrivacyInternal")
/** Get data from other spreadsheet
* @param{string} url of spreadsheet
* @param{string} sheet source sheetname
* @param{array} colarr array of columns
* @param{object} obj destination object
* @param{string} destination property name
* @return {object}destination object
*/
function GetSheetFromSS(url, sheet, colarr, obj, objprop) {
try {
let sheetname
let ID = getFileIdFromDriveUrl(url);
let SSname = DriveApp.getFileById(ID).getName();
let Sheets = SpreadsheetApp.openById(ID).getSheets();
for (i = 0; i < Sheets.length; i++) {
sheetname = Sheets[i].getName();
if (sheetname == sheet) {
if (!obj[objprop]) { obj[objprop] = {} }
var values = SpreadsheetApp.openById(ID).getSheetByName(sheetname).getDataRange().getValues();
// Extract headers
var headers = values[0];
// Extract the body
var body = values.slice(1);
// You need an index for looping through the body rows
var i = 0;
// For each header
headers.forEach(function (c) {
if(colarr.length!==0){
//select column to get data from
if (colarr.indexOf(c) !== -1) {
// Create an empty list for the values
obj[objprop][c] = [];
// For every row in the body assign the values to their properties
body.map(r => obj[objprop][c].push(r[i]));
i++;
}
}else{
// Create an empty list for the values take all columns
obj[objprop][c] = [];
// For every row in the body assign the values to their properties
body.map(r => obj[objprop][c].push(r[i]));
i++;
}
})
}
}
return (obj)
} catch (ex) {
Logger.log(ex);
}
}
Page updated
Report abuse