trackslkp.blogg.se

The biggest downside to this approach is that it takes (at least for me) approximately 7 full seconds for the onEdit trigger to catch the change and perform the update. You can then set an onEdit event trigger in the project's triggers under "Resources" in the script editor for your hotkey function. The test function is used to call it assigning 'Pound sterling' to the format parameter.One possible work around for this would be to dedicate a column for "trigger text", define different text based triggers for each action you're trying to perform, and then create a function that checks the value and performs an action based on the "trigger text". The setNumberFormat is a partially implemented Google Apps Script function that currently applies Pound sterling currency format ( #,#0.00) or a default format ( #,#0.00). First set the desired currency format for a cell by clicking on Format > More Formats > More Currencies, the click on Format > More Formats > Custom Number Formats. They could be got from the Google Sheets UI. The Google documentation doesn't include currency formats.

Conditional Formatting doesn't apply number formatting.A custom function can't be used to apply a number format as custom functions can't modified the cell format.To programmatically apply a currency format to a single number and to keep it as a number in order to do arithmetic operations with it use setNumberFormat(string). Does anyone know a working, direct way to do so via Google Apps Scripts or spreadsheet functions/menus? In short, I just want to add custom currency symbols to my numbers and still be able to do math and charts with them. This is more a workaround a not a clean solution. SetNumberFormat(format) cannot be used in a custom function to be called via spreadsheet formulas.Ĭopying another cells formatting? I also see this as a workaround but it looks really not efficient if I have to copy and paste formatting for every cell I want the proper formatting : Applying "Automatic" number formattingĬopying the values into another column just for conditional formatting and the original numbers only range used for maths/charting. If I create a custom function, say format(cell), and use JS to add any text strings to the value, it's no longer a number type.Įx: function GBP(amt).Spreadsheet function TEXT() can get the number format to display how I want but not all custom currency symbols "work" - the following for instance turns the value into a text string and I cannot do math/charts with it. If I use the spreadsheet function "CONCATENATE", I get a string and cannot do math or chart with the final value. I have looked at the following options already: I would like to programatically apply a custom number format so that I can use custom currency symbols in my cells (such as for cryptocurrency) and keep them as numbers so that I can do math with them or chart the values.