search

OfficeDev / office-js-docs-pr

Microsoft Office Add-ins Documentation
https://learn.microsoft.com/office/dev/add-ins
Creative Commons Attribution 4.0 International
403 stars 248 forks source link

Pivot Table Refresh Feature Not Available #3464

Closed ChulakaMK closed 2 years ago

ChulakaMK commented 2 years ago

Hi,

When we add new data to the database that data not automatically reach to pivot tables if we don't go to data and refresh all or refresh that particular pivot table.

"pivotLayout.preserveFormatting = True; " or " await context.sync(); " don't provide that feature output. please suggest a proper way to refresh all data in excel every time when we add data into database.

Thank you

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

AlexJerabek commented 2 years ago

Hi @ChulakaMK,

Thanks for reaching out. Office JavaScript APIs do not automatically refresh PivotTables. This matches Excel's behavior, as documented here: https://support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2. We strive to make the behavior of the JavaScript APIs the same as how Excel behaves when interacting with the UI.

In order to refresh a PivotTable after new values have been added, you'll need to either call PivotTable.refresh or PivotTableCollection.refreshAll. Automatically refreshing the PivotTable after values are added could be a value-add that your add-in brings to your customers.

If you would like this additional functionality, please provide that feedback through our Feedback Portal. There, you can explain your scenario and how it would be benefitted by automatic PivotTable refreshing.

I'm going to close this issue, since there's nothing actionable that we can do in the documentation. If I misinterpreted your issue, please respond on this thread.

ChulakaMK commented 2 years ago

Hi,

I'm working with Scriptlab extension of Excel to automate my excel to work in web. I'm using javascript to develop my code in scriptlab. the issue is there is no any keyword from refresh or refreshall inside of it. I go through your above links but it is not supportive with my case.

Thanks

AlexJerabek commented 2 years ago

@ChulakaMK,

Could you please provide us some information about your Excel environment?

ghost commented 2 years ago

Thanks for your interest in Office Add-ins development! Feedback here is intended for reporting problems with the Office Add-ins documentation. Can you please post this feature request to the Microsoft 365 Developer Platform Tech Community? Feature Requests submitted to Tech Community are regularly reviewed by the product teams as they plan future releases. Thanks!

ChulakaMK commented 2 years ago

Hi Alex.

Sorry for the late reply,

Issue: script Lab JavaScript doesn't available cording facility to refresh excel pivot tables

On Fri, Jun 10, 2022, 10:04 PM Alex Jerabek @.***> wrote:

@ChulakaMK https://github.com/ChulakaMK,

Could you please provide us some information about your Excel environment?

  • Platform [PC desktop, Mac, iOS, Office on the web]: __
  • Office version number: __
  • Operating System: __
  • Browser (if using Office on the web): __

— Reply to this email directly, view it on GitHub https://github.com/OfficeDev/office-js-docs-pr/issues/3464#issuecomment-1152540641, or unsubscribe https://github.com/notifications/unsubscribe-auth/AYVYNA4BGN5AIFQKMOZZ26LVONVCBANCNFSM5YAPKDCQ . You are receiving this because you were mentioned. Message ID: @.*** com>

AlexJerabek commented 2 years ago

Hi @ChulakaMK,

Thanks for the reply. I noticed something in your earlier post:

I'm working with Scriptlab extension of Excel to automate my excel to work in web.

Script Lab is only intended to explore and prototype Add-ins, it's not intended to be a full automation solution. If you're looking for an easy way to automate tasks in Excel for the web, may I recommend Office Scripts? It's similar to the Office JavaScript APIs, but easier to use and doesn't require developing a full add-in to share with others.

In case I'm wrong about your needs and you're looking to develop a full add-in, here is some sample code that shows how to refresh a PivotTable in Script Lab. I added these snippets to the "Create and modify" PivotTable sample to add a Refresh button to the task pane.

JS:

$("#refresh").click(() => tryCatch(refresh));
async function refresh() {
  await Excel.run(async (context) => {
    const pivotTable = context.workbook.pivotTables.getItem("Farm Sales");
    pivotTable.refresh();
    await context.sync();
  });
}

HTML:

<button id="refresh" class="ms-Button">
    <span class="ms-Button-label">Refresh the PivotTable</span>
</button>

Please let me know if any of this advice is helpful.

ChulakaMK commented 2 years ago

Hi Alex,

Really appreciate your great support to success my project,

but I'm afraid to tell you that code is not working with me.

I'll explain the requirement and hope that will helps you to understand the reason why I'm looking for refresh option.

I have a database and I'm adding data into the database daily basis. My pivot table reads the database and it should be update when I click the refresh. And other requirement is I need to remove (Filter) blank cells from the pivot table.

Please refer below example in Farm Data. New data

[" ", "Mango", "Conventional", 220, 1500], [" ", "Pineapple", "Organic", 150, 190], ["H Farms", "Lemon", "Conventional", 100, 270]

requirements

  1. both 3 data should appear in the pivot table once I refresh. it is the requirement of refresh button
  2. once I click filter I need to remove blank rows of farm. ("Mango" and "Pineapple" don't have a farm, I need to remove that from filter option.

I would like to use office script but issue is, that office script only works in web. some times I use desktop to reduce data entering time. at that time office script is not available. but script lab is working both web and pc everywhere.

If you have a connection with Microsoft please inform data validation drop-down lists are not properly working in mobile phones browsers when browser is open as desktop site. I've tested for crome, firefox, edge and tron. it's not only for mobile but also iPad. currently I'm clearing that cell and type half of the name in the dropdown and then select. There are 2 major issues.

  1. it can't select new selection and once I select it will select first data of the drop-down list.
  2. It don't allow to change a selected item.

currently I'm selecting that cell and clear the cell and type half of the name until that option is the only option then I select that option.

Thank you Chulaka Pitigala

AlexJerabek commented 2 years ago

Hi @ChulakaMK,

I think I know why the refresh isn't working in your scenario. The PivotTables in the samples are built using a specific range, instead of a table. This means that adding a row to the data isn't detected by the refresh. Please look at this sample that uses a table as the PivotTable source: https://gist.github.com/AlexJerabek/52effd9072fa3ab4c41db11f12cfda81

As for Office Scripts, it is possible to use Office Scripts with Windows. You need to use script buttons. Basically, Office Scripts can only be created and edited through the web client, but you can add a button in the workbook that runs a script when pressed. This lets desktop users run your script.

For the feedback about data validation, it's best to report that through Excel (Help > Feedback ). That collects all the information the product team needs to diagnose the problem.

Please let me know if this helps. Thanks!

ChulakaMK commented 2 years ago

Hi Alex,

Code worked and I finished my Project...

Thanks a lot