Forum Discussion

funkyfoenky's avatar
funkyfoenky
Copper Contributor
Dec 24, 2021

Need help on Excel Script: Show data from a query inside a new sheet

Hi all,   I need your help as I cannot record the actions I need to perform to generate script. I have a data query loaded in the excel workbook and I need it to be automatically be put in an exis...
BI & Data Analysis
Excel for web
Office Scripts
JKPieterse's avatar
JKPieterse
Silver Contributor
Dec 24, 2021

funkyfoenky This script takes the first table on the active sheet, inserts a new worksheet and pastes that table in the same location on the new sheet:

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let sourceTbl: ExcelScript.Table = selectedSheet.getTables()[0];
  let topLeftAddress = sourceTbl.getRange().getCell(0, 0).getAddress().split("!")[1]
  let newSht = workbook.addWorksheet();
  newSht.getRange(topLeftAddress).copyFrom(sourceTbl.getRange(), ExcelScript.RangeCopyType.values, false, false);
  let newTable = workbook.addTable(newSht.getRange(topLeftAddress).getSurroundingRegion(), true);
}
funkyfoenky's avatar
funkyfoenky
Copper Contributor
Dec 24, 2021
Thanks for the reply. Could be what I need, I will test.
Actually my real issue is when I load a table from a query and then sync my table with PowerApps, PowerApps creates a new column with some IDs inside and this is breaking the link between the table and the query and the table doesn't refresh anymore with new data.

Resources