Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 25, 2020 07:45 AM
I’ve been playing around with the scripting block, while learning Java script.
I’m trying to make a script to generate pivot tables.
I’m a little stock on understanding why my output.table(tableArray) in below code doesn’t make a nice table similar to my test table? Anyone that can see from my output what I’m doing wrong?
const table = base.getTable('report');
let queryResult = await table.selectRecordsAsync();
output.inspect(queryResult);
//Row names
let nameArray = []
for (let record of queryResult.records){
//nameArray.push(record.name)
nameArray.push(record.getCellValue('Select Name')[0].name)
};
const uniqueNames = [...new Set(nameArray)];
output.inspect(uniqueNames);
//Coloumn names
let itemsArray = []
for (let record of queryResult.records){
itemsArray.push(record.getCellValue('Working on'))
}
const uniqueItems = [...new Set(itemsArray)]
output.inspect(uniqueItems);
//Make Rows
let tableArray = [];
for(let i=0; i<uniqueNames.length; i++ ){
let categories = uniqueItems.map(record => {
return (' '+ record + ': ' + '£0')
})
nameItem = uniqueNames[i]
tableArray.push(`{Name/Item: ${nameItem}, ${categories}}`);
}
output.inspect(tableArray)
output.table(tableArray)
//Outcome - Test table
output.table([
{'Name/Item': 'John', Car: '', Bus: '£34', Scooter: '', '':''},
{'Name/Item': 'Dan', Car: '£32', Scooter: '£70', '':''},
{'Name/Item': 'Theo', Bus: '£90', Car: '£50'},
]);
My output:
Mar 25, 2020 08:02 AM
Yes, the first two arrays are simply data values, whereas, the pretty one is an array of JSON objects. And output.table()
want’s an array of JSON objects.
Mar 25, 2020 08:26 AM
Thank you @Bill.French, still early days on my Java Script journey.
Thank you for taking your time to answer my questions.
I got it to work passing real objects, i naively thought I could just put a string with some curly braces and an object would appear.
I got it to work with making a few for loops. Is there an easier/more straight forward way to do the below?
//Make Rows
let tableArray = []
for(let i=0; i<uniqueNames.length; i++ ){
let obj= {};
obj['Name/Item']=uniqueNames[i];
for(let i=0; i<uniqueItems.length; i++){
obj[uniqueItems[i]]=0;
}
output.inspect(obj)
tableArray.push(obj);
}
Mar 25, 2020 09:17 AM
No, I think that’s pretty elegant. And to add - the best code is neither elegant or correct; it is maintainable and understandable mostly by you.
Mar 26, 2020 05:12 AM
@Bill.French, do you know if there is any way to style the output table?
I’ve been trying different things with markdown, however I don’t really seem to be getting anywhere.
Mar 26, 2020 06:41 AM
Nope; not that I’m aware. Until they open access to the DOM and CSS in the script frame, this will be a challenge. It demonstrates the stark difference between Script Blocks and Custom Blocks.
The issue is security; any script block with unabated access to HTML, CSS, and the DOM could allow nefarious stuff to creep in and attack the entire Airtable infrastructure.
The only approach I have imagined is using script to generate images that render precise and well-formed displays of content.
Mar 26, 2020 11:35 PM
I guess I’ll have to work towards graduating to use Custom Blocks.
Jun 17, 2021 03:28 PM
Thanks for that info Bill, Scripts are great, but I guess I too will start to investigate Custom Apps/Blocks due to the output text/colour format limitations of scripts.
I was hoping their might be away to add row colour to every odd row of an output table, but doesn’t appear so. :frowning:
Jun 17, 2021 03:48 PM
Ya’ know - the earth has traveled 584 million miles since this thread was created and despite that, there’s still no remedy. :winking_face: Ironically, we’re back in the same location in space so, in a sense - we’re standing still.