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

Question

Hi all,&nbsp;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.&nbsp;Thank you in advance for your help!!

jkpieterse · Answer

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

jkpieterse · Answer

funkyfoenky&nbsp;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 · Answer

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.

funkyfoenky · Answer

JKPieterse&nbsp;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?

jkpieterse · Answer

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

Forum Discussion

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

8 Replies

Resources