adding hyperlinks to many cells

[walterbax]

wondering if the only way to set hyperlinks is to go cell by cell as needed?

i have a link or two in almost all rows, and i was hoping i could just set the cell contents in the query for it to transfer. so i set the data for those columns to =HYPERLINK("http://www.example.com/", "Go Here") but it isnt actually clickable until you edit and save the value again in excel. I feel like there has to be a simplier way then looking through each cell i need to set, but i havent come across it yet, hoping for some help to speed up my search! Thanks

[cfsimplicity]

When adding data sets to a spreadsheet you can specify what (Excel) data type to use for each column. From version 3.8.0 you can use url as the datatype and that should mean all values in the column are clickable links. Here's a very simple example.

spreadsheet = New spreadsheet.Spreadsheet();
outputPath = ExpandPath( "./hyperlinktest.xlsx" );
data = [ [ "https://www.google.com/" ], [ "https://www.bing.com/"] ];
queryObject = QueryNew( "URI", "VarChar", data );
workbook = spreadsheet.newXlsx();
spreadsheet
	.addRows( workbook=workbook, data=queryObject, datatypes={ url: [ "URI" ] } )
	.write( workbook, outputPath, true );

[walterbax]

thanks, i did notice this after i submitted my thread, but I would also like to set the text, so setCellFormula is what think is needed for this, unless there is a way to add this into your example aswell?

also, im guessing even though you only have one column that your syntax of passing datatypes in your example allow for only setting the ones i want to set, rather then a full list?

[cfsimplicity]

I would also like to set the text, so setCellFormula is what think is needed for this

Sorry, I'm not clear what you mean? Set the text of each hyperlink to be different to the URL? You'd need to use setCellHyperlink() on each cell to do that. It can't be done when passing in a dataset as there's no way of specifying both text and URL in a single data value. Not sure why you'd use setCellFormula()? Perhaps I've misunderstood you?

your syntax of passing datatypes in your example allow for only setting the ones i want to set, rather then a full list?

Yes, you can just specify the column(s) you want to control and leave the others to be auto-detected.

[walterbax]

I would also like to set the text, so setCellFormula is what think is needed for this

Sorry, I'm not clear what you mean? Set the text of each hyperlink to be different to the URL? You'd need to use setCellHyperlink() on each cell to do that. It can't be done when passing in a dataset as there's no way of specifying both text and URL in a single data value. Not sure why you'd use setCellFormula()? Perhaps I've misunderstood you?

Thanks for the quick responses!

I was thinking setCellFormula might be easier since i had already set the cell text to '=HYPERLINK("http://www.example.com/", "Go Here")' but i could also set the cell to a json or other way to pass both values, while looping over them to run setcellValue and setCellHyperlink.

in any case it looks like the answer to my question of is it possible without looping over all the rows, is no, if I want separate text other than the url to display as the link.