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

[funkyfoenky]

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 existing blank sheet as a table so I can view and refresh Data.

Thank you in advance for your help!!

[#2]

Do you mean you need a static copy of the query result?

[#3]

@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);
}

[#4]

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.

[#5]

@Jan Karel Pieterse It is working but it doesn't solve my final goal.

It is possible with script to Delete a table then map a new table based on a data connexion?

[#6]

Please write down all individual steps you require. Our, even better, explain in detail what you are trying to achieve

[#7]

@Jan Karel Pieterse 

OK here is what I need to perform:

I have 3 data queries inside a workbook: (sorry screenshot is in french)

From these queries I would like to create a table in A1 on a dedicated sheet (Deviation, CAPA, Other CAPA Plan) after updating the queries. Tables should be named Deviation_Table, CAPA_Table and Other_CAPA_Plan_Table respectively.

I usually create it by hand using Excel desktop application: (Example from Deviation query)

Thank you in advance for your help

[#8]

Are you saying you want to keep a static copy of each table before that table is refreshed?

[#9]

• Yes right! Actually I need to remove the static copy, refresh data source and then load a new static copy.