User Profile
SergeiBaklan
Diamond Contributor
Joined 10 years ago
User Widgets
Recent Discussions
Re: Script changing dates to text
As variant you may replace dates with texts in place, it could be like function main(workbook: ExcelScript.Workbook) { // https://techcommunity.microsoft.com/discussions/excelgeneral/script-changing-dates-to-text/4468085 fnDateToText(workbook.getActiveWorksheet().getRange("E:E").getUsedRange()) fnDateToText(workbook.getActiveWorksheet().getRange("H:H").getUsedRange()) } const fnDateToText = (columnRange: ExcelScript.Range) => { const converted = columnRange.getValues().map(x => { const scalar = x[0] let dateAsText: number | string | boolean = null if (typeof scalar === "number") { const d = new Date(Math.round((scalar - 25569) * 86400 * 1000)) dateAsText = d.getDate().toString().padStart(2, '0') + "/" + (d.getMonth() + 1).toString().padStart(2, '0') + "/" + d.getFullYear().toString() } else { dateAsText = scalar } return [dateAsText] }) columnRange.setNumberFormatLocal("@") columnRange.setValues(converted) } Function converts numbers (which dates actually are) in the column into text with date converted to "dd/mm/yyy". And ignores other values in such column.Re: Building relationships in data model to leverage power pivot - circumventing unique key ID issue
In general it's not necessary to build bridge table to handle many-to-many relationships, as variant you may use virtual relationships. based on your sample let assume we have two tables For them we to calculate COGS we may use measure COGSv:=CALCULATE( SUM(COGS[COGS] ), TREATAS( VALUES( Sales[Service Line] ), COGS[Service Line] ) ) which gives Compare with Total COGS:=SUM( COGS[COGS] ) More details at Physical and Virtual Relationships in DAX - SQLBI and you may google for other sources.Re: SORTBY newb... Other solutions don't appear to apply??
SortBy() takes the source range, sort it based on another array and put result into new place in the same or another sheet. That's practically no limit on the size of the source range. If to sort in place that could be done programmatically (VBA, OfficeScript) or manually by Data -> Sort. Here you may sort by helper column which map Urgent, High, etc on numerical order of which (e.g. using XLOOKUP); or here you may sort by color if that's more suitable. So, depends on goals, do you need to sort in place or copy sorted data into another place.Re: Excel conditional formula
Another comment, with const flattenedSource = sourceRange.getValues()[0] I assumed we work with rows, otherwise above doesn't work. In general , flat() works even if the editor reports the problem. Both of these const v = workbook.getActiveWorksheet().getUsedRange().getValues() const flattenedA: (number | string | boolean) [] = v.flat() const flattenedB: (number | string | boolean) [] = [] for (let row of v) { for (let cell of row) { flattenedB.push(cell) } } return exactly the same array.Re: Excel conditional formula
At least in Excel environment it shall be [1, "wxyz", true, 3].map((x: number) => x + 5) // => [6, wxyz5, 6, 8] Yes, editor reports a problem "type string is not assignable to type number" if we use something like const abc: number = "abc" but actually it works console.log(abc + 2, ~~abc + 2) // => abc2, 2 On the other hand yes, above is not a good practice which better to avoid. Just in case, whatever to number conversion table is here javascript - parseInt vs unary plus, when to use which? - Stack OverflowRe: Excel conditional formula
Another point it's hard to test with regex all valid addresses, from A1 to XFD1048576. Expression will be too complex. However, OfficeScript checks itself if we use correct range, thus we may rely on it and use try/catch. In addition it's only to add our own rule which doesn't allow to run script with single cell ranges. Something like function main( workbook: ExcelScript.Workbook, Range_of_references: string = "A2:F2", Range_to_color: string = "A3:F3", Increment: number = 5 ) // see https://techcommunity.microsoft.com/discussions/excelgeneral/excel-conditional-formula/4464321 { const worksheet = workbook.getActiveWorksheet() if (!Range_of_references.includes(":") || !Range_to_color.includes(":")) { throw new Error(`Keep ":" in range addresses`) } try { worksheet.getRange(Range_of_references) worksheet.getRange(Range_to_color) } catch (error) { console.log(`Incorrect (${Range_of_references}) or (${Range_to_color}) address. Please inspect and run again.`) return } const sourceRange = worksheet.getRange(Range_of_references) const targetRange = worksheet.getRange(Range_to_color) const flattenedSource = sourceRange.getValues()[0] const incremented = targetRange.getValues()[0].map(x => +x + Increment) targetRange.getFormat().getFill().clear() Array.from(incremented.keys()) .filter(n => incremented .map(x => flattenedSource.includes(x))[n]) .map(m => targetRange.getCell(0, m).getFormat().getFill().setColor("lightgreen")) }Re: Excel conditional formula
Great approach. Here is with slightly modified second part function main( workbook: ExcelScript.Workbook, Range_of_references: string = "A2:F2", Range_to_color: string = "A3:F3", Increment: number = 5 ) // see https://techcommunity.microsoft.com/discussions/excelgeneral/excel-conditional-formula/4464321 { function normalizeAddress(address: string): string { // Trim and convert to uppercase for consistent address validation return address.trim().toUpperCase(); } function isValidAddress(address: string): boolean { // Basic regex to validate Excel-style cell references (e.g., A1:B3, B10:C10...) const regex = /^[A-Z]+[1-9][0-9]*:[A-Z]+[1-9][0-9]*$/; return regex.test(normalizeAddress(address)); } // Validate the input address format (e.g., "A1", "B100", "AZ35") if (!isValidAddress(Range_of_references) || !isValidAddress(Range_to_color)) { // Execution stops on throw (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/throw) throw new Error("Please enter valid range address(es) like A1:D1, C4:G4, ..."); } // Get active worksheet and ranges const worksheet = workbook.getActiveWorksheet(); const sourceRange = worksheet.getRange(Range_of_references); const targetRange = worksheet.getRange(Range_to_color); ///////////////////////////////////////////////////////////////////// // Another variant const flattenedSource = sourceRange.getValues()[0] const incremented = targetRange.getValues()[0].map(x => +x + Increment) targetRange.getFormat().getFill().clear() Array.from( incremented.keys()) .filter(n => incremented .map(x => flattenedSource.includes(x))[n]) .map( m => targetRange.getCell(0, m).getFormat().getFill().setColor("lightgreen")) // end of another variant ///////////////////////////////////////////////////////////////////////// /* // Count columns in targetRange const targetColumns: number = targetRange.getColumnCount(); // Flatten sourceRange (workaround to .flat() that raises a warning in Excel TS console) const flattenedSource = sourceRange.getValues() .reduce((acc, row) => acc.concat(row), []); // Iterate cells in targetRange for (let i = 0; i <= targetColumns; i++) { const currentCell = targetRange.getCell(0, i); // Invoking read methods inside of a loop could lead to slow perf.: const currentValue = Number(currentCell.getValue()); // Iterate flattenedSource for (let j = 0; j <= targetColumns; j++) { // If currentValue (in targetRange) + Increment = current flattenedSource value... if (flattenedSource[j] === currentValue + Increment) { // Color the cell in targetRange currentCell.getFormat().getFill().setColor("lightgreen") // ("#C1F0C8"); break } } } */ }Re: Agent Mode continuously loading
That's not a preview feature. That's experimental feature provided as it is outside main Excel. Not necessary it'll be improved and/or ever appears in Excel as built-in functionality. Concrete question you may ask directly Excel Labs team here microsoft/Excel-Labs, sometimes they answer. I'd recommend first to check ReadMe and other issues, if nothing useful open your own issue.
