Appsheet Database integration to PowerBI

[Preethi]

Hi, 
I'm new to Appsheet and I have an issue as foolowing: 
I have an Appsheet with datasource as Appsheet database. I'm trying to create a report in PowerBI with the above database. I wanted to know if there's any way of integrating the Appsheet database to PowerBI or is there any workarounds where I can use the API or how can I use the built-in Automation bots for getting the data to get the data near real time.

@Suvrutt_Gurjar @dbaum @WillowMobileSys 

[SkrOYC · Accepted solution]

Here is the code to finally simplify things and give you a proper response:

let
    url = "https://api.appsheet.com/api/v2/apps/{appId}/tables/{tableName}/Action?applicationAccessKey=<applicationAccessKey>",
    body  = "{
""Action"": ""Find"",
""Properties"": {
   ""Locale"": ""en-US"",
   ""Location"": ""47.623098, -122.330184"",
   ""Timezone"": ""Pacific Standard Time""
},
""Rows"": [
]
}",
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
    #"Into a table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Into a table"

Then what is left is you to split the result adding a final step where you just select the columns you need.

PS: Remember that AppSheet will apply security filters, that's a topic for other day I guess
PS2: You can tell PowerBI that the authentication needed to conect is "Anonymous" since the auth token is in the code in this case 

[peterson]

Well, as far as I know, there`s no workaround for that, if you were using google sheets, maybe, but not good. My advise is USE GOOGLE LOOKER STUDIO (Previously named as Data Studio) it has native integration with appsheet and it reads your virtual columns too. Power BI is kinda better, but Looker Studio does the job. 

Here`s a link about it

https://support.google.com/appsheet/answer/12176287?hl=en

[WillowMobileSys]

---

@Preethi wrote:

I wanted to know if there's any way of integrating the Appsheet database to PowerBI

---

As already answered, no.  AppSheet Database is a proprietary system to appsheet specifically for AppSheet apps.   We are not yet able to make it available to outside sources.  Additionally, please be aware of the limitations of the AppSheet Database.  It may not suit your needs in the app to begin with.

The real question is what datasources can PowerBI connect to?  Once you know that then it is a matter of using the tools that allow the two to work together.  For instance, maybe PowerBI can connect to Google sheets.  If so, then it seems obvious to switch to Google sheets as the datasource for the app AND for PowerBI.

[SkrOYC]

Invoke the API - AppSheet Help

They made the AppSheet Databases feature work behind the AppSheet API, as if any source is being used.

[Preethi]

Hi, Thanks for the Reply.

I tried Invoking the API in postman, for which I got the following error :

I used this API : https://api.appsheet.com/api/v2/apps/{App ID}/tables/{Table Name in Unicode characters}/Action?ApplicationAccessKey={ApplicationAccessKey}

[WillowMobileSys]

Correct!  Presently, you cannot "pull" data from an AppSheet app.  An external service can only send "Adds and Updates into AppSheet.  OR...AppSheet can "push" data to PowerBI through their, PowerBI's, API if it is a REST API.  But you would need somewhere for that data to go to.

Your best option is to connect both AppSheet and PowerBI to the same datasource, if possible.  This is why I asked "what datasources can PowerBI connect to?". 

If PowerBI can connect to a Google sheet, or Excel as mentioned by @jaichith, and that is an acceptable approach for you, then that is the route you want to go.  There would be no need to create inefficient API processes to pass data back and forth. 

(NOTE:  I did a search and it seems that PowerBI, a Microsoft product, can use Excel as a datasource but I see no mention of Google Sheets)

I KNOW that both AppSheet and PowerBI can connect to a database, but I assume you are trying to avoid the extra cost that comes with standing up a database as a datasource.

[SkrOYC]

AppSheet can just send you the Rows from a given table after a POST request, not GET, that's why it's not intuitive.

The hard part would be to configure the POST request in PowerBI since you need to use a Blank Request and create the connection by your own. This video should somewhat help to understand the idea.

Read records from a table - AppSheet Help

PS: My bad on using the Web connector on PowerBI since it uses GET.

[SkrOYC · Accepted solution]

Here is the code to finally simplify things and give you a proper response:

let
    url = "https://api.appsheet.com/api/v2/apps/{appId}/tables/{tableName}/Action?applicationAccessKey=<applicationAccessKey>",
    body  = "{
""Action"": ""Find"",
""Properties"": {
   ""Locale"": ""en-US"",
   ""Location"": ""47.623098, -122.330184"",
   ""Timezone"": ""Pacific Standard Time""
},
""Rows"": [
]
}",
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
    #"Into a table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Into a table"

Then what is left is you to split the result adding a final step where you just select the columns you need.

PS: Remember that AppSheet will apply security filters, that's a topic for other day I guess
PS2: You can tell PowerBI that the authentication needed to conect is "Anonymous" since the auth token is in the code in this case 

[Preethi]

Thank you for the inputs, helped a lot!
I tried passing this M query but ended up getting the following error. 

Any thoughts? 

[SkrOYC]

Hey @Preethi !
I'm glad I look for this post since the notification of your comment got lost.

1. Make sure you start from a blank query and then paste my content into the advanced editor
2. Make sure you meet the requirements of your security filters

[WillowMobileSys]

---

@SkrOYC wrote:

They made the AppSheet Databases feature work behind the AppSheet API, as if any source is being used.

---

Yes, but how does that help get data FROM the AppSheet Database TO PowerBI??

[SkrOYC]

Yes. Using PowerBI's Web source it transforms the JSON response into a table

[jaichith]

Connect the Data base ( Google sheet ) to power bi else create excel as DB share the common db for both power bi and appsheet. 

[SkrOYC · Accepted solution]

Btw, here is an updated version I just made to make it easier to complete with your own data:

let
    AppId = "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
    Table = "TableNameHere",
    ApiKey = "xx-xxxxx-xxxxx-xxxxx-xxxxx-xxxxx-xxxxx-xxxxx-xxxxx",
    url = Text.Combine({"https://api.appsheet.com/api/v2/apps/", AppId, "/tables/", Table, "/Action?applicationAccessKey=", ApiKey}),
    body  = "{
""Action"": ""Find"",
""Properties"": {
   ""Locale"": ""en-US"",
   ""Location"": ""00.000000, 00.000000"",
   ""Timezone"": ""Pacific Standard Time"",
   ""UserSettings"": {
      ""Option 1"": ""SomeText"",
      ""Option 2"": ""SomeText""
    }
},
""Rows"": [
]
}",
    Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] )),
    #"Into a table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Into a table"

Just replace with your data inside the AppId, Table and ApiKey variables 

[BMacheroOrtiz]

hola;

intente usar tu consulta pesonalizada pero me dio el siguiente error, como se podria resolver?

Expression.Error: No se reconoce el nombre 'application'. Asegúrese de que se ha escrito correctamente.

= Json.Document(Web.Contents(url,[Headers = [#"Content-Type"=application/json], Content = Text.ToBinary(body) ] ))

[SkrOYC]

Te faltan comillas.
Intenta copiar el código tal como lo compartí

[BMacheroOrtiz]

Incluyo las comillas dobles? 

[SkrOYC]

Así es

[BMacheroOrtiz]

Hola, disculpa que solicite tu ayuda nuevamente pero me quede en esto y no pude continuar estoy iniciando en Power BI y me serviría mucho sobrepasar este paso para poder continuar con mi informe.

[SkrOYC]

Te sugiero revisar los recursos de PowerBI para poder resolver tus dudas específicamente sobre esa plataforma