How to automatically login and refresh a spreadsheet using a plug-in?
Hi. I have a requirement to automatically refresh a spreadsheet's data. I'm an Excel novice and have no idea how to do this automatically. To refresh the data in a manual fashion, I perform the following steps: 1) Open the Excel file. 2) Click on the iLEVEL menu option (it's on the top amongst other menu options such as 'Data', 'Review', Automate', etc..). This will present an iLEVEL login icon. Click on this icon and then enter my email address and password. In fact, once I've entered my work email, it uses SSO to gain access instead of entering a password. I do have a password should I need it (needs to be entered somewhere). 3) On the toolbar ribbon, under the iLEVEL menu option, there is the 'Refresh All' option. I click on this, wait a few minutes, and the spreadsheet has its data refreshed. iLEVEL is a third-party software solution. What I need to do is to achieve the above result automatically. At, say, 3am each morning, I would like automatically refresh the data. So there is the need to not only automatically login and refresh the data, but also to schedule the data refresh. The updated spreadsheet will then act as the data source for our reporting. In case it's asked, at this time, we cannot look to hook into the view/table that feeds this spreadsheet. Therefore, the spreadsheet is the endpoint. I will investigate this but if you are aware if this can be done or not, and point me in the right direction, it will be appreciated. I'm wondering if Power Query, or something similar, could achieve this. Thanks.How to automate a login and refresh of a spreadsheet via an Excel plug-in?
Hi. I have a requirement to automatically refresh a spreadsheet's data. I'm an Excel novice and have no idea how to do this automatically. To refresh the data in a manual fashion, I perform the following steps: 1) Open the Excel file. 2) Click on the iLEVEL menu option (it's on the top amongst other menu options such as 'Data', 'Review', Automate', etc..). This will present an iLEVEL login icon. Click on this icon and then enter my email address and password. In fact, once I've entered my work email, it uses SSO to gain access instead of entering a password. I do have a password should I need it (needs to be entered somewhere). 3) On the toolbar ribbon, under the iLEVEL menu option, there is the 'Refresh All' option. I click on this, wait a few minutes, and the spreadsheet has its data refreshed. iLEVEL is a third-party software solution. What I need to do is to achieve the above result automatically. At, say, 3am each morning, I would like automatically refresh the data. The updated spreadsheet will then act as the data source for our reporting. In case it's asked, at this time, we cannot look to hook into the view/table that feeds this spreadsheet. Therefore, the spreadsheet is the endpoint. I will investigate this but if you are aware if this can be done or not, and point me in the right direction, it will be appreciated. I'm wondering if Power Query, or something similar, could achieve this. Thanks.
Can you create a Button that Opens a Pop-Up with Data from Spreadsheets
Good evening, I have moderate knowledge of excel, and some knowledge of visual basic and am trying to develop a personal trading journal log. My intent is to create a pop-up for trades that I can input a lot of detailed data and an image of the trade that will then auto-populate a brief information row for a quick glance, but will also have a button in the row that populates for each trade that, if clicked, will open a pup-up of all the information given including the picture, comments about the trade in reflection and review, etc. I have been searching for if this is possible and haven't really found any information on it, only things like pop-ups for inputting data. Can anyone help me determine if this is possible, and if so how? Thank you.
Excel 2024 stops responding with macro workbook from previous Excel version
Hello, I have an issue related to contract work I do. The main contractor creates a workbook that has macros for several rows in the workbook. I've used these workbooks for years running Excel 2016. I had to get a new laptop and so I had to get the newest version of Office. I bought Office when I bought the laptop and installed it. When I try to input data in the row with macros, Excel freezes (not responding) and I have to close it using the task manager. Sometimes it will crash all together, but mostly it freezes. I've tried contacting Microsoft support via chat and they insisted there was a problem with workbook and was not a compatibility issue between the older and new version of Excel. This has happened after I enabled macros. I have tried updating Excel and am currently trying to update Windows 11. Has anyone experienced this and is so is there a fix? I did try searching for this issue prior to creating this conversation. Thanks in advance for any help.
Custom number formatting help
Hello, I need some help/advice for displaying a value. Im scaling large recipe amounts, and at some point it'll become cumbersome to do '112 tbsps' and I'd prefer it read '7cups' after a threshold. Ive got a lot happening in an office script that I've made and Ideally I'd like to avoid further complicating the core formula of these cells. So I was looking at number formatting as a possible solution. However, Im struggling to figure out if its possible. Right now I can obviously check for the thresholds, but the formatting itself doesn't seem to do any of the actual math. For example [>=32] #/16 "cup(s)";# ?/? "tbsp" just returns 1792/16 cup(s). Rather than divide the value, it scales it up by 16 which is... confusing. Could someone tell me what I'm missing? Or am I looking in the wrong direction here trying to use the number formatting and instead I should work in the CONVERT function into my cell formula? I'm hoping to avoid that, so I thought I'd ask for help. Thanks!
Shared office scripts - Needs premission everytime it's executed
I wrote some office scripts in some excel workbooks. Those scripts are executed by pressing a button created with what excel provides. Co-workers in my company are supposed to use these scripts frequently. Whenever they execute any script they are being asked if they trust the author, then they press allow and the scripts executes. As far as I can tell this message (of trust) is supposed to be asked just for the first time any script is executed or after anything changed with a script. However in my company they are being asked everything single time (even after nothing changed or repeating executions after another with any same script). We already tested some stuff in the Trust Center but no success yet. What can I do to just get the message once or after every update of the script? Note: This behavior even takes place with scripts that just print "Hello World" in the console.
Office Script works on Web but provides a wrong result on Excel Desktop for updating formulas
I built an Office script to add FYXX (FY26, FY27, etc.) columns to three excel tables, and to expand the formulas accordingly for the following year. The script works as expected in the web browser but when I run the same Script in my Excel Desktop (Current Channel), the formulas don't get updated correctly in the last two Excel tables (where they have formulas). I suspect it is related to the refresh process during the Script execution, I followed some recommendations on the web on how to overcome this well-known problem, but I am not able to fix it. (most of the tricks are commented in the source code, because I could not make it work. The problem can be isolated in the following inner function: function addFYXXCols(): void { // Logic for adding FYXX columns // Define the new column names for (var i = 1, addColNames = []; i <= colsNum; i++) { addColNames.push(FYCOL_PREFIX + (lastYearCounter + i)) } const lastCol = table.getColumnByName(lastFYXXColName) const lastRng = lastCol.getRangeBetweenHeaderAndTotal() const ROWS = lastRng.getRowCount() const regExUpdateFormula = new RegExp(`\\[${lastFYXXColName}\\]`, "g") const UPDATE_FORMULA = TABLE_NAME_SUFFIX_FORMULA.includes(tableName) const POS = lastFYXXColIdx + 1 // column position where to add the new column // Checking FYXX columns exist in COST table, before adding them to TCOST or HC Excel tables if (UPDATE_FORMULA) { const COST_TABLE_NAME = tableNames[0] const missingCols = findMissingCols(workbook.getTable(COST_TABLE_NAME), addColNames) if (missingCols.length > 0) { const MSG = `For adding '${addColNames}' columns in '${tableName}' they need to exist first in '${COST_TABLE_NAME}'. Please add missing columns '${missingCols}' and run it again.` Logger.error(MSG) } } Logger.trace(`Adding columns '${addColNames}' to Excel table: '${tableName}'.`) for (let j = 0; j < addColNames.length; j++) { // Iterating by new columns to add const COL_NAME = addColNames[j] const col = table.addColumn(POS + j) const newColRange = col.getRangeBetweenHeaderAndTotal() col.setName(COL_NAME) // Rename directly on the reference //table.getRangeBetweenHeaderAndTotal().getText() // forcing re-fresh of the table if (UPDATE_FORMULA) { // The formula needs to be updated for (let i = 0; i < ROWS; i++) { // iterating by rows const lastCell = lastRng.getCell(i, 0) const FORMULA = lastCell.getFormula() if (FORMULA) { // if it has a formula, then update it const NEW_FORMULA = FORMULA.replace(regExUpdateFormula, `[${COL_NAME}]`) const newCell = newColRange.getCell(i, 0) //newCell.setValue("") // prep the cell //table.getRangeBetweenHeaderAndTotal().getUsedRange() newCell.setFormula(NEW_FORMULA) //newCell.getUsedRange() // A force-refresh trigger, to make Excel "commit" } } // end for iterating by rows } // end if //flushTable(table) //table.getRangeBetweenHeaderAndTotal() // A force-refresh trigger, to make Excel "commit" } // end for iterating by columns } // End function I am attaching the Excel file, as you can see the formulas are not correct for the added columns FY26, FY27 in the tables TB_P4TCOST, TB_P4HC they are not consistent with the previous column for the same row, the only change by row should be the reference to the new added columns. I could not load the Office Script, so I am adding it the Excel file, under the tab Office Script. It tested using the Office Script input parameters just modifying one of the tables with formula (table input argument: TOTAL_COST, HEADCOUNT for example), to check if there is any conflict processing more than one Excel Table, but the problem remains the same. Here the screenshot of the Excel table, where the formulas are not correct. The helper function flushTable, that is commented, was one of the solution I found on internet, but calling it didn't solve the problem: /*Trick to force flush the table*/ function flushTable(table: ExcelScript.Table) { const dummyCol = table.addColumn() dummyCol.setName("_temp") dummyCol.getRangeBetweenHeaderAndTotal().setValue("refresh") dummyCol.delete() } The Logger is a singleton class I created to handle the logging process. Since it is an inner function it inherits variables and functions from the main/outer function. There is no issue with the source code since it works as expected in Web Excel, so it is a specific platform/version problem, I am looking for some workaround on how to fix it so it works for Excel Desktop too. Any help is appreciated, thanks in advance for your help.Chart for Library Student Occupency Chart
I have study library of 70 seating capacity. All data is only in this sheet. I want to create a chart in which I get libraray seat occupancy status by colour. Whenever seat is occupied then Green colour else Red colour. If a seat with Full shift, is displayed by large circle and if with Morning or Evening shift is displayed by small circle, and inside the circle seat number. Status is automatically update when column J's date gone.Write an office script to create two table
I want to ask how to write an office script to make the main table to two tables like the picture. Thanks! The main table is the expense claims from employees. I need to split it into two tables for our finance department. Thanks! I have attached the Excel document.
