none
Unable to get property 'getScrollTop' of undefined or null reference RRS feed

  • Question

  • Hello,

    along with these frowns:

    Unexpected Error - Specified cast is not valid

    Unexpected Error - Could not find a PackageSession for the given sessionID

    I am getting this new frown: Unable to get property 'getScrollTop' of undefined or null referenceLike in the cases mentioned before, this frown occur unexpectedly. It stops the VBA macro that refreshes the query and therefore stops the whole daily process, that must then be handled manually.

    I would really appreciate any idea how to avoid this frown.

    I am also wondering what does it exactly says? What is getScrollTop ?

    Warm regards

    Feedback Type:
    Frown (Error)
    
    Error Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    Stack Trace:
       at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.InvokeScript(String script, Boolean bypassDebugHost)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.NavigatorHost.TryInvokeHostedScript[T](String methodName, T& result, Object[] args)
       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)
       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)
       at Microsoft.Mashup.Host.Document.Model.Queries.ChangeScope..ctor(Queries queries, PackagePath packagePath, PackageReference packageReference, Boolean commitEachEdit)
       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)
       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.<>c__DisplayClass72_0.<ForEachFillSessionByWorkbook>b__1()
      at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.UpdateQueriesProgress()
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__10_0()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
    
    Stack Trace Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    Invocation Stack Trace:
       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    InnerException.Stack Trace Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    InnerException.Stack Trace:
    TypeError: Unable to get property 'getScrollTop' of undefined or null reference
       at eval code (eval code:1:1)
    
    InnerException.Invocation Stack Trace:
       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.OnFillUpdateTimerTick(Object sender, EventArgs eventArgs)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    Supports Premium Content:
    True
    
    Formulas:
    
    
    section Section1;
    
    shared DOHODY = let
        Source = Folder.Files("G:\HR CR_SK\HR IT\Sensitive\NUGGET"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TESTMHDP.CSV") and ([Folder Path] <> "G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\")),
        #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from NUGGET", each #"Transform File from NUGGET"([Content])),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Folder Path", "Transform File from NUGGET"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from NUGGET", Table.ColumnNames(#"Transform File from NUGGET"(#"Sample File"))),
        buffer = Table.Buffer(#"Expanded Table Column1"),
        #"Added Custom" = Table.AddColumn(buffer, "PODNIK", each Text.End( List.Last( List.RemoveNulls( List.RemoveMatchingItems ( Text.Split([Folder Path],"\"),{""}))),1)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Folder Path"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"PODNIK", "Osobní číslo", "měsíc", "rok", "Jméno", "Vynětí z ES", "FTE", "Smlouva", "Důvod vynětí", "Datum ukončení", "Důvod ukončení praco", "Návrat do ES", "Entita", "Věk", "Pohlaví", "Profese", "Úsek", "Grade", "Datum nástupu", "Důvod nástupu", "První nástup", "Plán. ukonč. PP", "Nadřízený", "Level", "Datum ukonč. ZD", "Oddělení", "VPP", "Základní měsíční pla", "Oddělní", "Profese(funkce)", "Litujeme", "Nadřízený_1", "L5081", "Důvod ukončení praco_2", "Hodinová sazba (tari"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"PODNIK", Int64.Type}, {"Osobní číslo", Int64.Type}, {"měsíc", Int64.Type}, {"rok", Int64.Type}, {"Jméno", type text}, {"Vynětí z ES", type date}, {"FTE", type number}, {"Smlouva", type text}, {"Důvod vynětí", type text}, {"Datum ukončení", type date}, {"Důvod ukončení praco", Int64.Type}, {"Návrat do ES", type date}, {"Entita", Int64.Type}, {"Věk", type number}, {"Pohlaví", type text}, {"Profese", type text}, {"Úsek", type text}, {"Grade", type text}, {"Datum nástupu", type date}, {"Důvod nástupu", Int64.Type}, {"První nástup", type date}, {"Plán. ukonč. PP", type date}, {"Nadřízený", Int64.Type}, {"Level", Int64.Type}, {"Datum ukonč. ZD", type date}, {"Oddělení", type text}, {"VPP", Int64.Type}, {"Základní měsíční pla", type number}, {"Oddělní", Int64.Type}, {"Profese(funkce)", Int64.Type}, {"Litujeme", type text}, {"Nadřízený_1", type text}, {"L5081", type text}, {"Důvod ukončení praco_2", type text}, {"Hodinová sazba (tari", type number}, {"Druh pracovněprávníh", type number}, {"Křestní", type text}, {"Příjmení", type text}, {"Plat nekrácený", type number}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"PODNIK"},ENTITY2,{"Podnik"},"ENTITY",JoinKind.LeftOuter),
        #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Entita", "Stred"}}),
        #"Expanded ENTITY" = Table.ExpandTableColumn(#"Renamed Columns", "ENTITY", {"Entita"}, {"Entita"}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Expanded ENTITY",{"PODNIK", "Entita", "Osobní číslo", "měsíc", "rok", "Jméno", "Vynětí z ES", "FTE", "Smlouva", "Důvod vynětí", "Datum ukončení", "Důvod ukončení praco", "Návrat do ES", "Stred", "Věk", "Pohlaví", "Profese", "Úsek", "Grade", "Datum nástupu", "Důvod nástupu", "První nástup", "Plán. ukonč. PP", "Nadřízený", "Level", "Datum ukonč. ZD", "Oddělení", "VPP", "Základní měsíční pla", "Oddělní", "Profese(funkce)", "Litujeme", "Nadřízený_1", "L5081", "Důvod ukončení praco_2", "Hodinová sazba (tari"}),
        #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"Osobní číslo", "ID"}, {"měsíc", "mesic"}, {"Jméno", "jmeno"}, {"Důvod vynětí", "vyneti"}, {"Datum ukončení", "datumKonec"}, {"Důvod ukončení praco", "duvodKonecID"}, {"Návrat do ES", "datumNavratES"}, {"Úsek", "usek"}, {"Grade", "grade"}, {"Datum nástupu", "datumNastup"}, {"Důvod nástupu", "duvodNastup"}, {"Nadřízený", "mngID"}, {"Oddělení", "oddeleni"}, {"Profese(funkce)", "idPos"}, {"Nadřízený_1", "mngJmeno"}, {"Důvod ukončení praco_2", "duvodKonec"}, {"Hodinová sazba (tari", "sazba"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns2", each [datumKonec] = null or (Date.StartOfMonth([datumKonec]) >= #date([rok], [mesic], 1))),
        #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Stred] <> 621) ),
        #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"ID", Order.Ascending}, {"rok", Order.Ascending}, {"mesic", Order.Ascending}}),
        #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "obdobi", each #date([rok],[mesic],1), type date),
        #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"ID"}, EMP2, {"ID"}, "EMP", JoinKind.LeftOuter),
        #"Expanded EMP" = Table.ExpandTableColumn(#"Merged Queries1", "EMP", {"corpKey"}, {"CK"}),
        #"Merged Queries2" = Table.NestedJoin(#"Expanded EMP", {"mngID"}, EMP2, {"ID"}, "EMP", JoinKind.LeftOuter),
        #"Expanded EMP1" = Table.ExpandTableColumn(#"Merged Queries2", "EMP", {"corpKey"}, {"mngCK"}),
        #"Reordered Columns2" = Table.ReorderColumns(#"Expanded EMP1",{"PODNIK", "Entita", "ID", "mesic", "rok", "jmeno", "Vynětí z ES", "FTE", "Smlouva", "vyneti", "datumKonec", "duvodKonecID", "datumNavratES", "Stred", "Věk", "Pohlaví", "Profese", "usek", "grade", "datumNastup", "duvodNastup", "První nástup", "Plán. ukonč. PP", "mngID", "Level", "Datum ukonč. ZD", "oddeleni", "VPP", "Základní měsíční pla", "Oddělní", "idPos", "Litujeme", "mngJmeno", "L5081", "duvodKonec", "sazba", "obdobi", "CK", "mngCK", "Druh pracovněprávníh", "Křestní", "Příjmení", "Plat nekrácený"}),
        #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns2",{{"Druh pracovněprávníh", "SmlouvaDruh"}, {"Plat nekrácený", "Plat1FTE"}})
    in
        #"Renamed Columns3";
    
    shared ENTITY = let
        Source = Access.Database(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"), [CreateNavigationProperties=true]),
        _ENTITY = Source{[Schema="",Item="ENTITY"]}[Data]
    in
        _ENTITY;
    
    shared #"Sample File" = let
        Source = Folder.Files("G:\HR CR_SK\HR IT\Sensitive\NUGGET"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TESTMHDP.CSV") and ([Folder Path] <> "G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\")),
        Navigation1 = #"Filtered Rows"{0}[Content]
    in
        Navigation1;
    
    shared #"Sample File Parameter1" = #"Sample File" meta [IsParameterQuery=true, BinaryIdentifier=#"Sample File", Type="Binary", IsParameterQueryRequired=true];
    
    shared #"Transform Sample File from NUGGET" = let
        Source = Csv.Document(#"Sample File Parameter1",[Delimiter=";", Columns=38, Encoding=1250, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Jméno_3", "Křestní"}})
    in
        #"Renamed Columns";
    
    [ FunctionQueryBinding = "{""exemplarFormulaName"":""Transform Sample File from NUGGET""}" ]
    shared #"Transform File from NUGGET" = let
        Source = (#"Sample File Parameter1" as binary) => let
            Source = Csv.Document(#"Sample File Parameter1",[Delimiter=";", Columns=38, Encoding=1250, QuoteStyle=QuoteStyle.None]),
            #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
            #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Jméno_3", "Křestní"}})
        in
            #"Renamed Columns"
    in
        Source;
    
    shared EMP = let
        Source = Access.Database(File.Contents("G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"), [CreateNavigationProperties=true]),
        _EMPLOYEE = Source{[Schema="",Item="EMPLOYEE"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(_EMPLOYEE,{"ID", "corpKey"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([corpKey] <> ""))
    in
        #"Filtered Rows";
    
    shared EMP2 = let
        Source = ( OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"";", "select [_].[ID],
        [_].[corpKey]
    from 
    (
        select [ID],
            [corpKey]
        from [EMPLOYEE] as [$Table]
    ) as [_]
    where [_].[corpKey] <> '' or [_].[corpKey] is null"))
    in
        Source
    ;
    
    shared ENTITY2 = let
        Source = ( OleDb.Query("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""G:\HR CR_SK\HR IT\Sensitive\DWH\DWH.accdb"";", "select [$Table].[Podnik] as [Podnik],
        [$Table].[Entita] as [Entita],
        [$Table].[Zkratka] as [Zkratka],
        [$Table].[Stred] as [Stred],
        [$Table].[Nazev1] as [Nazev1],
        [$Table].[Aktivni] as [Aktivni],
        [$Table].[NazevOF] as [NazevOF],
        [$Table].[IC] as [IC],
        [$Table].[adresa] as [adresa],
        [$Table].[Popis] as [Popis],
        [$Table].[mzdovka] as [mzdovka],
        [$Table].[F12] as [F12],
        [$Table].[F13] as [F13],
        [$Table].[F14] as [F14],
        [$Table].[F15] as [F15]
    from [ENTITY] as [$Table]"))
    in
        Source;
    
    shared #"DOHODY (2)" = let
        Source = Folder.Files("G:\HR CR_SK\HR IT\Sensitive\NUGGET"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TESTMHDP.CSV") and ([Folder Path] <> "G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\")),
        #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from NUGGET", each #"Transform File from NUGGET"([Content])),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Folder Path", "Transform File from NUGGET"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from NUGGET", Table.ColumnNames(#"Transform File from NUGGET"(#"Sample File")))
    in
        #"Expanded Table Column1";
    
    shared #"DOHODY (3)" = let
        Source = Folder.Files("G:\HR CR_SK\HR IT\Sensitive\NUGGET"),
        #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "TESTMHDP.CSV") and ([Folder Path] <> "G:\HR CR_SK\HR IT\Sensitive\NUGGET\MZDY100\")),
        #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
        #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from NUGGET", each #"Transform File from NUGGET"([Content])),
        #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
        #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1",{"Folder Path", "Transform File from NUGGET"}),
        #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from NUGGET", Table.ColumnNames(#"Transform File from NUGGET"(#"Sample File"))),
        buffer = Table.Buffer(#"Expanded Table Column1"),
        #"Added Custom" = Table.AddColumn(buffer, "PODNIK", each Text.End( List.Last( List.RemoveNulls( List.RemoveMatchingItems ( Text.Split([Folder Path],"\"),{""}))),1)),
        #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Folder Path"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"PODNIK", "Osobní číslo", "měsíc", "rok", "Jméno", "Vynětí z ES", "FTE", "Smlouva", "Důvod vynětí", "Datum ukončení", "Důvod ukončení praco", "Návrat do ES", "Entita", "Věk", "Pohlaví", "Profese", "Úsek", "Grade", "Datum nástupu", "Důvod nástupu", "První nástup", "Plán. ukonč. PP", "Nadřízený", "Level", "Datum ukonč. ZD", "Oddělení", "VPP", "Základní měsíční pla", "Oddělní", "Profese(funkce)", "Litujeme", "Nadřízený_1", "L5081", "Důvod ukončení praco_2", "Hodinová sazba (tari"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"PODNIK", Int64.Type}, {"Osobní číslo", Int64.Type}, {"měsíc", Int64.Type}, {"rok", Int64.Type}, {"Jméno", type text}, {"Vynětí z ES", type date}, {"FTE", type number}, {"Smlouva", type text}, {"Důvod vynětí", type text}, {"Datum ukončení", type date}, {"Důvod ukončení praco", Int64.Type}, {"Návrat do ES", type date}, {"Entita", Int64.Type}, {"Věk", type number}, {"Pohlaví", type text}, {"Profese", type text}, {"Úsek", type text}, {"Grade", type text}, {"Datum nástupu", type date}, {"Důvod nástupu", Int64.Type}, {"První nástup", type date}, {"Plán. ukonč. PP", type date}, {"Nadřízený", Int64.Type}, {"Level", Int64.Type}, {"Datum ukonč. ZD", type date}, {"Oddělení", type text}, {"VPP", Int64.Type}, {"Základní měsíční pla", type number}, {"Oddělní", Int64.Type}, {"Profese(funkce)", Int64.Type}, {"Litujeme", type text}, {"Nadřízený_1", type text}, {"L5081", type text}, {"Důvod ukončení praco_2", type text}, {"Hodinová sazba (tari", type number}, {"Druh pracovněprávníh", type number}}),
        #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"PODNIK"},ENTITY2,{"Podnik"},"ENTITY",JoinKind.LeftOuter),
        #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Entita", "Stred"}}),
        #"Expanded ENTITY" = Table.ExpandTableColumn(#"Renamed Columns", "ENTITY", {"Entita"}, {"Entita"}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Expanded ENTITY",{"PODNIK", "Entita", "Osobní číslo", "měsíc", "rok", "Jméno", "Vynětí z ES", "FTE", "Smlouva", "Důvod vynětí", "Datum ukončení", "Důvod ukončení praco", "Návrat do ES", "Stred", "Věk", "Pohlaví", "Profese", "Úsek", "Grade", "Datum nástupu", "Důvod nástupu", "První nástup", "Plán. ukonč. PP", "Nadřízený", "Level", "Datum ukonč. ZD", "Oddělení", "VPP", "Základní měsíční pla", "Oddělní", "Profese(funkce)", "Litujeme", "Nadřízený_1", "L5081", "Důvod ukončení praco_2", "Hodinová sazba (tari"}),
        #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"Osobní číslo", "ID"}, {"měsíc", "mesic"}, {"Jméno", "jmeno"}, {"Důvod vynětí", "vyneti"}, {"Datum ukončení", "datumKonec"}, {"Důvod ukončení praco", "duvodKonecID"}, {"Návrat do ES", "datumNavratES"}, {"Úsek", "usek"}, {"Grade", "grade"}, {"Datum nástupu", "datumNastup"}, {"Důvod nástupu", "duvodNastup"}, {"Nadřízený", "mngID"}, {"Oddělení", "oddeleni"}, {"Profese(funkce)", "idPos"}, {"Nadřízený_1", "mngJmeno"}, {"Důvod ukončení praco_2", "duvodKonec"}, {"Hodinová sazba (tari", "sazba"}}),
        #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns2", each [datumKonec] = null or (Date.StartOfMonth([datumKonec]) >= #date([rok], [mesic], 1))),
        #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each ([Stred] <> 621) ),
        #"Sorted Rows" = Table.Sort(#"Filtered Rows2",{{"ID", Order.Ascending}, {"rok", Order.Ascending}, {"mesic", Order.Ascending}}),
        #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "obdobi", each #date([rok],[mesic],1), type date),
        #"Merged Queries1" = Table.NestedJoin(#"Added Custom1", {"ID"}, EMP2, {"ID"}, "EMP", JoinKind.LeftOuter),
        #"Expanded EMP" = Table.ExpandTableColumn(#"Merged Queries1", "EMP", {"corpKey"}, {"CK"}),
        #"Merged Queries2" = Table.NestedJoin(#"Expanded EMP", {"mngID"}, EMP2, {"ID"}, "EMP", JoinKind.LeftOuter),
        #"Expanded EMP1" = Table.ExpandTableColumn(#"Merged Queries2", "EMP", {"corpKey"}, {"mngCK"}),
        #"Reordered Columns2" = Table.ReorderColumns(#"Expanded EMP1",{"PODNIK", "Entita", "ID", "mesic", "rok", "jmeno", "Vynětí z ES", "FTE", "Smlouva", "vyneti", "datumKonec", "duvodKonecID", "datumNavratES", "Stred", "Věk", "Pohlaví", "Profese", "usek", "grade", "datumNastup", "duvodNastup", "První nástup", "Plán. ukonč. PP", "mngID", "Level", "Datum ukonč. ZD", "oddeleni", "VPP", "Základní měsíční pla", "Oddělní", "idPos", "Litujeme", "mngJmeno", "L5081", "duvodKonec", "sazba", "obdobi", "CK", "mngCK", "Druh pracovněprávníh"}),
        #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns2",{{"Druh pracovněprávníh", "SmlouvaDruh"}})
    in
        #"Renamed Columns3";
    

      

    Wednesday, September 25, 2019 8:34 AM

All replies

  • Any help please?

    What does the frown says?

    Tuesday, October 8, 2019 8:28 AM
  • Bumping this up too. I'm having the same exact issue while trying to automatically refresh a query with VBA and task scheduler.
    Wednesday, October 9, 2019 4:52 PM
  • Hi Jakub,

    We have an open bug for this issue and the team is investigating. I'll keep you posted.


    Guy Hunkin - Excel Team

    Wednesday, October 16, 2019 9:30 AM
  • I have exactly the same issue. Using Excel of O365 of in our company. 70% of my automatic reports are hanging with the above error message an waiting for a manual "Cancel" click. Tried already several things, killing open EXCEL.EXE Processes before as well as Microsoft.Mashup.Container.NetFX40.exe. No luck. Is there no temporary workaround using VBA to prevent this problem for time beeing?
    Wednesday, October 23, 2019 5:41 AM
  • Jakub, _hapi_,

    Could one of you please provide a way to reproduce the issue? This will help us very much with investigating the issue.

    Thanks in advance!

    Thursday, October 24, 2019 11:00 AM
  • Hello,

    unfortunately not, the frown happens only sometimes across different files and queries.

    All I can do is "send a frown" which is what I do.


    This frown prompt is MODAL and must be closed (query loads successfully after closing), while for example "Could not find a PackageSession for the given sessionID" has a NON-MODAL frown prompt and macros can continue to work.

    Yehezkel, Could you make ALL frown prompts NON-MODAL? And/Or make .Refresh VBA command to return error instead of frown prompt that cannot be handled?

    Warm regards


    • Edited by jakub dusek Thursday, October 24, 2019 2:38 PM
    Thursday, October 24, 2019 2:37 PM
  • Same Problem here, our entire user update process is stuck and requires manual input.

    Please require workaround or fix.

    Also the issue is random.... no regular path.

    # This Script Convert the DSE XLSX data into a CSV file , ready for SFTP import
    Function ExcelCSV ($File)
    {
    
        $pwd = "\\IESWDBMSFSP001\SHARED02\Technical_Training\PUBLIC\REPORT\USER_FEED"
    
        $excelFile = "$pwd\" + $File + ".xlsx"
    
        $Excel = New-Object -ComObject Excel.Application
    
        $Excel.Visible = $false
    
        $Excel.DisplayAlerts = $false
    
        $wb = $Excel.Workbooks.Open($excelFile)
    
        foreach ($ws in $wb.Worksheets)
    
        {
    
            $ws.SaveAs("$pwd\" + "DSE_USER_FEED_SKB" + ".csv", 6)
    
        }
    
        $Excel.Quit()
    
    }
    $FileName = "DSE_USER_FEED_SKB"
    ExcelCSV -File $FileName
    $app = New-Object -comobject Excel.Application
    $app.Visible = $False
    $app.DisplayAlerts = $FALSE
    $wb = $app.Workbooks.Open("\\IESWDBMSFSP001\SHARED02\Technical_Training\PUBLIC\REPORT\USER_FEED\USER_DATA_ISSUE.xlsx")
    $wb.Name
    $wb.RefreshAll()
    $wb.Save()
    $app.Quit()
    $wb = $app.Workbooks.Open("\\IESWDBMSFSP001\SHARED02\Technical_Training\PUBLIC\REPORT\USER_FEED\USER_DATA_ISSUE.xlsx")
    $WorksheetRange = $workSheet.UsedRange
    $ws = $wb.Sheets.Item(1)
    $rows = $ws.UsedRange.Rows.Count
    $wb.Save()
    $app.Quit()
    kill -processname excel
    if($rows -gt 1){Send-MailMessage -From 'lmsadmin@sk.com' -To 'una.gaines@sk.com', 'luca.avalle@sk.com' -SmtpServer 'mailie.skbiotek.com' -Subject 'Error in User Data' -Body 'Please Review the Report here: \\IESWDBMSFSP001\SHARED02\Technical_Training\PUBLIC\REPORT\USER_FEED\USER_DATA_ISSUE.xlsx' }else {$app.Quit()
    }

    Monday, December 2, 2019 1:35 PM
  • Running into this same problem.  Running a python loop that includes a step where it opens Excel and runs some VBA macros that refresh data.  The loop will run multiple cycles for a while and then randomly throw this error during one of the VBA macros.

    Would love to hear of a cause or fix.

    Wednesday, February 5, 2020 5:42 PM
  • Hi,

    To help us investigate this issue, we need to know if the error you are receiving is 'getScrollTop' or another error. If it is not 'getScrollTop', please write us the error you receive.

    Also, if possible, please copy the stack trace.

     

    Thanks,

    Inbar, Excel team

    Sunday, February 16, 2020 4:05 PM
  • Same issue here. Random and occurs during automated process to refresh data model. Process is refreshing multiple workbooks and will work fine one day, next day will throw the error on workbook B, next day workbook D, etc. Basically, it's not consistent. The modal frown error box will sit there until manually closed defeating automation. Once closed, refresh process continues as expected. Please fix the bug, identify a workaround or make this error non-modal so it doesn't stop the refresh.

    Feedback Type:
    Frown (Error)
    
    Error Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    Stack Trace:
       at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)
       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)
       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)
       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__10_0()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
    
    Stack Trace Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    Invocation Stack Trace:
       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Delegate.DynamicInvokeImpl(Object[] args)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
       at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
       at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    InnerException.Stack Trace Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    InnerException.Stack Trace:
    TypeError: Unable to get property 'getScrollTop' of undefined or null reference
       at eval code (eval code:1:1)
    
    InnerException.Invocation Stack Trace:
       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Delegate.DynamicInvokeImpl(Object[] args)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
       at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
       at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    Supports Premium Content:
    True
    
    Formulas:
    
    
    section Section1;
    
    shared #"Client Historical" = let
        Source = Sql.Databases("SQL01"),
        DWH = Source{[Name="DWH"]}[Data],
        dbo_ClientHistoricalJet_V = DWH{[Schema="dbo",Item="ClientHistoricalJet_V"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(dbo_ClientHistoricalJet_V,{"date_key", "client_id", "provider_id", "client_funding_id", "staff_id", "locus_id"})
    in
        #"Removed Other Columns";
    
    shared Provider = let
        Source = Sql.Databases("SQL01"),
        DWH = Source{[Name="DWH"]}[Data],
        dbo_Provider_V = DWH{[Schema="dbo",Item="Provider_V"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(dbo_Provider_V,{"Provider ID", "Provider Name", "Provider Category"})
    in
        #"Removed Other Columns";
    
    shared #"Client Funding" = let
        Source = Sql.Databases("SQL01"),
        DWH = Source{[Name="DWH"]}[Data],
        #"dbo_Client Funding_V" = DWH{[Schema="dbo",Item="Client Funding_V"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(#"dbo_Client Funding_V",{"Client Insurance ID", "Policy Record Type"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Policy Record Type", "Insurance Record Type"}})
    in
        #"Renamed Columns";
    
    shared #"Client LOCUS Score" = let
        Source = Sql.Databases("SQL01"),
        DWH = Source{[Name="DWH"]}[Data],
        #"dbo_Client LOCUS Score_V" = DWH{[Schema="dbo",Item="Client LOCUS Score_V"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(#"dbo_Client LOCUS Score_V",{"LOCUS ID", "locus_disposition"})
    in
        #"Removed Other Columns";
    
    shared Staff = let
        Source = Sql.Databases("SQL01"),
        DWH = Source{[Name="DWH"]}[Data],
        dbo_Staff_V = DWH{[Schema="dbo",Item="Staff_V"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(dbo_Staff_V,{"Staff ID", "Staff Name", "Supervisor Name", "Staff Status", "Staff Type"})
    in
        #"Removed Other Columns";
    
    shared #"Client Demographics" = let
        Source = Sql.Databases("SQL01"),
        DWH = Source{[Name="DWH"]}[Data],
        #"dbo_Client Demographics_V" = DWH{[Schema="dbo",Item="Client Demographics_V"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(#"dbo_Client Demographics_V",{"Client ID", "Age in Years", "Correction Status", "Education Level", "Employment Status", "Gender", "Hispanic Ethnicity", "Language", "Living Arrangement", "Marital Status", "Religion", "Veteran Status", "Referral Source", "CRSP", "Disability Designation", "Funding Source", "MCPN", "Client Status"})
    in
        #"Removed Other Columns";
    

    Tuesday, March 10, 2020 2:04 PM
  • I have the same issue. As described by others already, the issue occurs seemingly at random when the refresh is called from an automated program (in my case a vb script rather than vba).

    Feedback Type:
    Frown (Error)
    
    Error Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    Stack Trace:
       at Microsoft.Mashup.Client.UI.Windows.JsErrorHandler.WrapInvokeScript(Func`1 invokeScript)
       at Microsoft.Mashup.Client.UI.Windows.JsHostedScript.TryInvoke[T](String script, T& result)
       at Microsoft.Mashup.Client.Excel.WorkbookQueriesNavigatorHost.GetScrollTop()
       at Microsoft.Mashup.Client.UI.Shared.Ux.Navigator.QueriesNavigatorModelBase.OnQueriesContentChangeStarted(Object sender, EventArgs e)
       at Microsoft.Mashup.Host.Models.EventHandlerExtensions.RaiseEvent(EventHandler handler, Object sender)
       at Microsoft.Mashup.Host.Document.Model.Queries.CreateChangeScope(Boolean commitEachEdit)
       at Microsoft.Mashup.Client.UI.Shared.Model.QueriesUtilities.ForEachWithChangeScope[T](IEnumerable`1 items, Func`2 getQueries, Action`1 action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.Microsoft.Mashup.Client.Excel.Shim.IDeferredStorageInvoker.InvokeDeferredStorageAction(IWorkbook workbook, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbook workbook, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Shim.NativeCoAuthServices.InvokeCoauthAction(IWorkbookIdentity workbookIdentity, UndoableActionType actionType, Action action)
       at Microsoft.Mashup.Client.Excel.Fill.FillManager.ForEachFillSessionByWorkbook(Action`1 action)
       at Microsoft.Mashup.Client.Excel.Fill.PassiveFillManager.<OnFillUpdateTimerTick>b__10_0()
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
    
    Stack Trace Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    Invocation Stack Trace:
       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Delegate.DynamicInvokeImpl(Object[] args)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
       at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
       at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    InnerException.Stack Trace Message:
    Unable to get property 'getScrollTop' of undefined or null reference
    
    InnerException.Stack Trace:
    TypeError: Unable to get property 'getScrollTop' of undefined or null reference
       at eval code (eval code:1:1)
    
    InnerException.Invocation Stack Trace:
       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.GetFeedbackValuesFromException(Exception e, String prefix)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo.CreateAdditionalErrorInfo(Exception e)
       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass0_1.<SendAndMarshalExceptions>b__0(Object null)
       at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       at System.Delegate.DynamicInvokeImpl(Object[] args)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       at System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       at System.Windows.Forms.Control.MarshaledInvoke(Control caller, Delegate method, Object[] args, Boolean synchronous)
       at System.Windows.Forms.Control.Invoke(Delegate method, Object[] args)
       at System.Windows.Forms.WindowsFormsSynchronizationContext.Send(SendOrPostCallback d, Object state)
       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       at System.Windows.Forms.Timer.OnTick(EventArgs e)
       at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.HostedWebBrowserSite.Microsoft.Mashup.Client.UI.Windows.HostedWebBrowser.IInternetSecurityManager.GetSecurityId(String pwszUrl, IntPtr pbSecurityId, UInt32& pcbSecurityId, UInt32 dwReserved)
    
    
    Supports Premium Content:
    True
    
    Formulas:
    
    
    section Section1;
    
    shared GetView = /*
    	Downloads Saved View/Advanced Find via Web API
    	Expand All Columns to access the returned results. Columns prefixed "OData.Community.Display.V1.FormattedValue" show the properly formatted values.
    */
    (OrgUrl as text, QueryName as text, LogicalCollectionName as text, UserView as logical) =>
    let 
    
    	GetQueryByName =
    		(OrgUrl as text, QueryName as text, UserView as logical) => 
    			let
    				QueryType = if UserView then "user" else "saved"
    				,return = OData.Feed(
    						OrgUrl & "/api/data/v8.0/" & QueryType & "queries?$select="& QueryType & "queryid&$filter=name eq '" & QueryName & "'"
    					)[userqueryid]{0}
    		
    			in
    				return,
    	QueryAll = 
    		(nextURL, prev) => 
    			let
    				prevList = if prev <> null then prev else {},
    				responseData = Json.Document(Web.Contents(nextURL, [Headers=[Prefer="odata.include-annotations=""OData.Community.Display.V1.FormattedValue"""]])),
    				return = if responseData[#"@odata.nextLink"]? <> null then @QueryAll(responseData[#"@odata.nextLink"], prevList & responseData[value]) else responseData[value] & prevList
    			in return,
    	NamedQuery = OrgUrl & "/api/data/v8.0/" & LogicalCollectionName & "?userQuery=" & GetQueryByName(OrgUrl, QueryName, UserView),
    	return = Table.FromList(QueryAll(NamedQuery, null), Splitter.SplitByNothing(), null, null, ExtraValues.Error) 
    in return;
    
    shared Enquiries = let
        Source = GetView("https://xxx.crm4.dynamics.com", if InvokedStartAndEndDates{0}[StartDate] = Date.StartOfDay(DateTime.LocalNow()) then "Enquiries today for power query" else "Enquiries this month and last month for power query", "new_enquiries", true),
        #"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"new_contactmethod@OData.Community.Display.V1.FormattedValue", "new_producttype@OData.Community.Display.V1.FormattedValue", "createdon@OData.Community.Display.V1.FormattedValue", "new_anonymous@OData.Community.Display.V1.FormattedValue", "_new_estateagent_value", "new_enquiryid", "new_clientservicecentrelead@OData.Community.Display.V1.FormattedValue", "_new_lead_value", "new_valuation@OData.Community.Display.V1.FormattedValue", "emailaddress", "new_homephone", "new_mobilenumber", "_new_estateagentcontact_value@OData.Community.Display.V1.FormattedValue", "_new_opportunity_value", "new_estateagentcontacthub", "new_searchcriteria", "_new_partnerproperty_value", "new_utmsource", "new_utmmedium", "new_utmcampaign", "a_994595ef075be911a843000d3ab2d3d5_x002e_name", "a_994595ef075be911a843000d3ab2d3d5_x002e_new_companylookup@OData.Community.Display.V1.FormattedValue", "a_994595ef075be911a843000d3ab2d3d5_x002e_new_areadirectorregionalmanagerlookup@OData.Community.Display.V1.FormattedValue", "a_994595ef075be911a843000d3ab2d3d5_x002e_new_brandlookup@OData.Community.Display.V1.FormattedValue", "a_d92b2716125be911a843000d3ab2d3d5_x002e_createdon@OData.Community.Display.V1.FormattedValue", "a_d92b2716125be911a843000d3ab2d3d5_x002e_new_createdonforreporting@OData.Community.Display.V1.FormattedValue", "_createdby_value@OData.Community.Display.V1.FormattedValue"}, {"ContactMethod", "ProductType", "EnquiryCreatedOn", "Anonymous", "PartnerAccountId", "EnquiryId", "ClientServiceCentreEnquiry", "LeadId", "Valuation", "EmailAddress", "HomePhone", "MobilePhone", "PartnerContactLookup", "OpportunityId", "PartnerContactString", "SearchCriteria", "PartnerPropertyId", "UtmSource", "UtmMedium", "UtmCampaign", "PartnerAccountName", "PartnerCompany", "PartnerAreaManager", "PartnerBrand", "LeadCreatedOn", "LeadCreatedOnForReporting", "CreatedBy"}),
        #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"ContactMethod", type text}, {"ProductType", type text}, {"Anonymous", type text}, {"PartnerAccountId", type text}, {"EnquiryId", type text}, {"ClientServiceCentreEnquiry", type text}, {"LeadId", type text}, {"Valuation", type text}, {"EmailAddress", type text}, {"HomePhone", type text}, {"MobilePhone", type text}, {"PartnerContactLookup", type text}, {"OpportunityId", type text}, {"PartnerContactString", type text}, {"SearchCriteria", type text}, {"PartnerPropertyId", type text}, {"UtmSource", type text}, {"UtmMedium", type text}, {"UtmCampaign", type text}, {"PartnerAccountName", type text}, {"PartnerCompany", type text}, {"PartnerAreaManager", type text}, {"PartnerBrand", type text}, {"EnquiryCreatedOn", type datetime}, {"LeadCreatedOnForReporting", type datetime}, {"CreatedBy", type text}, {"LeadCreatedOn", type datetime}}),
        #"Added Conditional Column1" = Table.AddColumn(#"Changed Type", "EnquiryDateTime", each if [EnquiryCreatedOn] < #datetime(2019, 4, 18, 0, 0, 0) then (if [LeadCreatedOnForReporting] = null then (if [LeadCreatedOn] = null then [EnquiryCreatedOn] else [LeadCreatedOn]) else (if [LeadCreatedOn] = null then [LeadCreatedOnForReporting] else (if [LeadCreatedOn] < [LeadCreatedOnForReporting] then [LeadCreatedOn] else [LeadCreatedOnForReporting]))) else [EnquiryCreatedOn], type datetime),
        #"Inserted Date" = Table.AddColumn(#"Added Conditional Column1", "EnquiryDate", each DateTime.ToText(Date.StartOfDay([EnquiryDateTime]),"yyyy-MM-ddTHH:mm:ss"), type text),
        #"Filtered Rows" = Table.SelectRows(#"Inserted Date", each [EnquiryDateTime] >= Date.AddDays(InvokedStartAndEndDates{0}[StartDate],-1) and [EnquiryDateTime] < InvokedStartAndEndDates{0}[EndDate]),
        #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"EnquiryId"}, Yesterday, {"EnquiryId"}, "Yesterday", JoinKind.LeftOuter),
        #"Expanded Yesterday" = Table.ExpandTableColumn(#"Merged Queries", "Yesterday", {"EnquiryId"}, {"Yesterday.EnquiryId"}),
        #"Filtered Rows1" = Table.SelectRows(#"Expanded Yesterday", each ([Yesterday.EnquiryId] = null)),
        #"Added Conditional Column" = Table.AddColumn(#"Filtered Rows1", "PartnerContact", each if [PartnerContactLookup] = null then [PartnerContactString] else [PartnerContactLookup], type text),
        CharsToRemove = List.Transform({32..47,58..126}, each Character.FromNumber(_)),
        #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column", "PhoneNumber1", each if [HomePhone] = null then Text.Remove([MobilePhone],CharsToRemove) else Text.Remove([HomePhone],CharsToRemove), type text),
        #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "PhoneNumber2", each Text.Combine({"0", Text.TrimStart([PhoneNumber1], {"0", "4"})}), type text),
        #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "PhoneNumber", each if [PhoneNumber1] = null then null else if Text.StartsWith([PhoneNumber1], "0044") then [PhoneNumber2] else if Text.StartsWith([PhoneNumber1], "044") then [PhoneNumber2] else if Text.StartsWith([PhoneNumber1], "44") then [PhoneNumber2] else [PhoneNumber1], type text),
        #"Inserted Merged Column" = Table.AddColumn(#"Added Conditional Column4", "CombinedUtm", each Text.Combine({[UtmMedium], [UtmSource], [UtmCampaign]}, "_"), type text),
        #"Removed Other Columns" = Table.SelectColumns(#"Inserted Merged Column",{"ContactMethod", "ProductType", "Anonymous", "PartnerAccountId", "EnquiryId", "ClientServiceCentreEnquiry", "LeadId", "Valuation", "EmailAddress", "OpportunityId", "SearchCriteria", "PartnerPropertyId", "UtmSource", "UtmMedium", "UtmCampaign", "PartnerAccountName", "PartnerCompany", "PartnerAreaManager", "PartnerBrand", "CreatedBy", "EnquiryDateTime", "EnquiryDate", "PartnerContact", "PhoneNumber", "CombinedUtm"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","#(cr)","",Replacer.ReplaceText,{"SearchCriteria"}),
        #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","#(lf)","",Replacer.ReplaceText,{"SearchCriteria"}),
        #"Added Prefix" = Table.TransformColumns(#"Replaced Value1", {{"PhoneNumber", each "#" & _, type text}}),
        #"Extracted Date" = Table.TransformColumns(#"Added Prefix",{{"EnquiryDateTime", each DateTime.ToText(_,"yyyy-MM-ddTHH:mm:ss"), type text}}),
        #"Filtered Rows2" = Table.SelectRows(#"Extracted Date", each [EnquiryId] <> null and [EnquiryId] <> ""),
        Custom1 = try #"Filtered Rows2" otherwise null
    in
        Custom1;
    
    shared Yesterday = let
        DateTextFileName = Date.ToText(Date.AddDays(Date.From(InvokedStartAndEndDates{0}[StartDate]),-1),"yyyy_M_d"),
        Source = Csv.Document(File.Contents("V:\xxx\ArchiveTextFiles\Enquiries\" & DateTextFileName & ".txt"),[Delimiter="	", Columns=34, Encoding=1252, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        #"Removed Other Columns" = try Table.SelectColumns(#"Promoted Headers",{"EnquiryId"}) otherwise #table({"EnquiryId"},{{null}})
    in
        #"Removed Other Columns";
    
    shared StartAndEndDates = let
        Source = (optional StartDate as date, optional EndDate as date)=>
    let
        //StartDate = #date(2018,1,31),
        //EndDate = #date(2018,1,31),
        #"Converted to Table" = #table({"StartDate"}, {{if StartDate = null then DateTime.LocalNow() else StartDate}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"StartDate", type datetime}}),
        #"Calculated Start of Day" = Table.TransformColumns(#"Changed Type",{{"StartDate", Date.StartOfDay, type datetime}}),
        #"Inserted Date" = Table.AddColumn(#"Calculated Start of Day", "EndDate", each if EndDate = null then DateTime.LocalNow() else EndDate),
        #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Date",{{"EndDate", type datetime}}),
        #"Calculated End of Day" = Table.TransformColumns(#"Changed Type1",{{"EndDate", Date.EndOfDay, type datetime}})
    in
        #"Calculated End of Day"
    in
        Source;
    
    shared InvokedStartAndEndDates = let
        Source = StartAndEndDates(null, null)
    in
        Source;


    Friday, March 27, 2020 11:56 AM
  • Hi,

    We have an open bug on this issue. Our team is still investigating it. We will update you on any further developments.

     

    Thanks,

    Inbar, Excel team

    Sunday, May 10, 2020 8:54 AM
  • Hi,

    We have a solution for you: if you write in a macro 'Application.DisplayAlerts = false' then the unhandled exceptions window will not pop up.

    This update will be available from build: 16.0.13105.35904

    Thanks,

    Inbar, Excel team


    Tuesday, July 28, 2020 7:30 AM