クラシックファイルタイプは2021年1月をもって使用できなくなりました。クラシックファイルを移行することも、PDFをダウンロードすることもできます。詳細はこちら

Spreadsheet refresh connection fails

回答済み
デモをリクエスト
  • 編集済み

When trying to manually refresh a spreadsheet connection to a WData query it fails (image below).

The connection was working fine previously and no changes have been made to the query or the sheet. 

I've also checked that the amount of cells retrieved by the query is far below the maximum (2 million). In fact, if I set a connection to the same query on a different spreadsheet it works ok. 

How can I find the root cause of the refresh issue?

0

コメント

5件のコメント

日付 投票
  • Jeff Hickey

    Hi Leandro, if you hover your mouse over the orange triangle, you can get further information of the failure in the tooltip message. If that information does not help to resolve the issue, try running the query directly in Wdata.

    If it successfully runs in Wdata, go back to your Spreadsheet and look for any locked cells and destination linked cells in the Sheet. You can quickly visualize locked cells by clicking the "Show Overlay" button. Locked cells will cause the update to fail.

    Finally, Spreadsheets as a whole are limited to 2 million cells. This includes active and historically used cells in all Sheets within the Spreadsheet. An indirect test to check if you're exceeding the limit is to attempt to connect the Query to a completely new Spreadsheet. If successful, then your query is producing two million cells or less, but more cell than are available in the original Spreadsheet. In this case you can either reduce the query output, delete unused Sheets from the Spreadsheet, delete entire unused rows/columns (not individual cells) in Sheets, or use a new Spreadsheet.

    0
  • Leandro Casal
    • 編集済み

    Thanks for your reply Jeff Hickey.

    Regarding this comment: "Finally, Spreadsheets as a whole are limited to 2 million cells. This includes active and historically used cells in all Sheets within the Spreadsheet."

    Does that mean that if I run many times a unique connection refresh that retrieves 200k records, in the 11th attempt I'll be surpassing the 2 million records due to historical data? Could you please let me know if this is correct?

    Thanks, regards

    0
  • Jeff Hickey

    Hi Leandro, you can write to the same 200k cells as many times as you'd like since it is less than 2 million. Each Spreadsheet can use a maximum of 2 million cells, and the data in those 2 million cells can be changes as much as needed. In your example, if your Query produces 200k cells of data, then the Spreadsheet would have 800k cells remaining to be used elsewhere. If more than 800k cells are in use elsewhere, then your Query would not be able to write it's 200k cells of data since the 2 million cell limit would be exceeded.

    A historically used cell is one that had data in it previously but now does not. This blank cell will continue to count toward the 2 million cell allocation so that its history can be maintained. Deleting the row and/or column that the cell is in will remove it and its history and free it from counting toward the max cell limit. Deleting unused rows, column, and Sheets are examples of ways to fee up cell count in a Spreadsheet if needed.

    0
  • Leandro Casal

    Thanks

    0
  • Jonathon Daley
    • 編集済み

    Hello, 

    Just tacking onto this thread - has anyone ever encountered this type of error message when refreshing a worksheet connection? 

    This appears to be different than the message I get when I have a cell count limitation. The query in wData is returning results; however upon refreshing the connection in the source workbook, I am getting this message. 

    Any shared insight would be greatly appreciated - thank you! 

     

    2

サインインしてコメントを残してください。