calderonsamuel/appsheet

Functionality to add, delete, read and update table records from your 'AppSheet' apps, using the official API

appsheet

The goal of appsheet is to provide an easy way to use the Appsheet API to retrieve, add, update and delete rows from your app tables.

The package exports a main function called appsheet(), which you can use to perform all the supported actions. A supporting ash_properties() function allows you to customize the expected input/output.

Have in mind that there is no evidence that the API will also work well with slices and that appsheet() returns all the columns as character vectors.

Installation

You can install the stable version of appsheet from CRAN.

install.packages("appsheet")

Also, you can install the development version of appsheet from GitHub with:

# install.packages("remotes")
remotes::install_github("calderonsamuel/appsheet")

Authentication

The first step is to Enable the API for cloud-based service communication. Once this is done you should have:

1. The App ID. Use it in the appId argument of appsheet() or via the APPSHEET_APP_ID environmental variable.
2. The Application Access Key. Use it in the access_key argument of appsheet() or via the APPSHEET_APP_ACCESS_KEY environmental variable.

The appsheet() function looks for both environmental variables by default.

Example

Here are some examples on how to perform the four basic operations. It all starts with loading the package.

library(appsheet)

Read a table

The first argument of appsheet() is a table name. By default, appsheet() will use the “Find” action, which reads all the rows. The following code is the equivalent of using appsheet(tableName = "Driver", Action = "Find").

appsheet("Driver")
#> # A tibble: 7 × 7
#>   `_RowNumber` Key      `Driver Name` Photo           Email `Phone Number` Jobs 
#>   <chr>        <chr>    <chr>         <chr>           <chr> <chr>          <chr>
#> 1 2            70608c66 Driver 1      Driver_Images/… driv… 1-206-555-1000 db9e…
#> 2 3            261fadec Driver 2      Driver_Images/… driv… 1-206-555-1001 36a4…
#> 3 4            525982c5 Driver 3      Driver_Images/… driv… 1-206-555-1002 1db9…
#> 4 5            90eb1244 Driver 4      Driver_Images/… driv… 1-206-555-1003 e367…
#> 5 6            ddb26f78 Driver 5      Driver_Images/… driv… 1-206-555-1004 5420…
#> 6 7            29671cfb Driver 6      Driver_Images/… driv… 1-206-555-1005 98ed…
#> 7 8            7a6fafca Driver 7      Driver_Images/… driv… 1-206-555-1006 0b64…

When the action is “Find”, you can take advantage of the Selector argument of ash_properties(), which can use some AppSheet internal functions to narrow the output.

appsheet(
    tableName = "Driver", 
    Properties = ash_properties(Selector = 'Filter(Driver, [Key] = "70608c66")')
)
#> # A tibble: 1 × 7
#>   `_RowNumber` Key      `Driver Name` Photo           Email `Phone Number` Jobs 
#>   <chr>        <chr>    <chr>         <chr>           <chr> <chr>          <chr>
#> 1 2            70608c66 Driver 1      Driver_Images/… driv… 1-206-555-1000 db9e…

Add records to a table

The “Add” action allows to add one or multiple records to a table. You must provide Rows, which can be a dataframe with the same column names as the specified table. You don’t need to provide all the columns to be successful, but can’t exclude the ones required by your app. Also, don’t try to add the _RowNumber(or Row ID when using an AppsSheet database), as it is generated internally.

An “Add” action returns a data.frame with the added rows when successful.

row_key <- paste0(sample(letters, 8), collapse = "") # to be reused 

appsheet(
    tableName = "Driver",
    Action = "Add",
    Rows = tibble::tibble(
        Key = row_key, # required in app logic
        `Email` = "[email protected]" # required in app logic
    ) 
)
#> # A tibble: 1 × 7
#>   `_RowNumber` Key      `Driver Name` Photo Email           `Phone Number` Jobs 
#>   <chr>        <chr>    <chr>         <chr> <chr>           <chr>          <chr>
#> 1 9            dgxshjuf ""            ""    driverXX@compa… ""             ""

Update records from a table

The “Edit” action allow to update values from one or multiple records from a table, it also can target multiple columns. This one also requires the Rows argument. Again, you can’t use the _RowNumber column (but in this one you can use the Row ID generated by an Appsheet database).

An “Edit” action returns a data.frame with the whole content of the updated rows when successful.

appsheet(
    tableName = "Driver",
    Action = "Edit",
    Rows = tibble::tibble(
        Key = row_key,
        `Driver Name` = "Some name",
        Photo = "some/path.jpg"
    ) 
)
#> # A tibble: 1 × 7
#>   `_RowNumber` Key      `Driver Name` Photo         Email   `Phone Number` Jobs 
#>   <chr>        <chr>    <chr>         <chr>         <chr>   <chr>          <chr>
#> 1 9            dgxshjuf Some name     some/path.jpg driver… ""             ""

Delete records from a table

The “Delete” action allows to delete one or multiple records from a table. This one also requires the Rows argument. Again, you can’t use the _RowNumber column (but in this one you can use the Row ID generated by an Appsheet database).

A “Delete” action returns a data.frame with the deleted rows when successful.

appsheet(
    tableName = "Driver",
    Action = "Delete",
    Rows = tibble::tibble(
        Key = row_key
    ) 
)
#> # A tibble: 1 × 7
#>   `_RowNumber` Key      `Driver Name` Photo         Email   `Phone Number` Jobs 
#>   <chr>        <chr>    <chr>         <chr>         <chr>   <chr>          <chr>
#> 1 9            dgxshjuf Some name     some/path.jpg driver… ""             ""

appsheet's Issues

Error in `FUN()`: ! Columns 1, 2, 3, 4, 5, and 5 more must be named.

Received this error when using the "Edit" action. The edit was successful but it wasn't possible to parse the response as tibbles

<error/tibble_error_column_names_cannot_be_empty>
Error in FUN():
! Columns 1, 2, 3, 4, 5, and 5 more must be named.
Use .name_repair to specify repair.
Caused by error in repaired_names():
! Names can't be empty.
✖ Empty names found at locations 1, 2, 3, 4, 5, etc.

Backtrace:
▆

1. ├─appsheet::appsheet(tableName = "vehicles", Action = "Edit", Rows = attempt_1)
2. │ └─response %>% lapply(tibble::as_tibble) %>% purrr::list_rbind() at appsheet/R/appsheet.R:49:8
3. ├─purrr::list_rbind(.)
4. │ └─purrr:::check_list_of_data_frames(x)
5. │ └─vctrs::vec_check_list(x, call = error_call)
6. │ └─vctrs::obj_check_list(x, ..., arg = arg, call = call)
7. └─base::lapply(., tibble::as_tibble)
8. ├─tibble (local) FUN(X[[i]], ...)
9. └─tibble:::as_tibble.list(X[[i]], ...)

10. └─tibble:::lst_to_tibble(x, .rows, .name_repair, col_lengths(x))
    

11.   └─tibble:::set_repaired_names(...)
    

12.     └─tibble:::repaired_names(...)
    

Error in successful add action when using appsheet database

Received this error, but the add action was successful.

appsheet("actas", Action = "Add", Rows = actas_to_load)
Error in FUN():
! All columns in a tibble must be vectors.
✖ Column _RowNumber is NULL.
Run rlang::last_trace() to see where the error occurred.

Release appsheet 0.1.0

First release:

• usethis::use_cran_comments()
• Update (aspirational) install instructions in README
• Proofread Title: and Description:
• Check that all exported functions have @return and @examples
• Check that Authors@R: includes a copyright holder (role 'cph')
• Check licensing of included files
• Review https://github.com/DavisVaughan/extrachecks

Prepare for release:

• git pull
• Check if any deprecation processes should be advanced, as described in Gradual deprecation
• devtools::build_readme()
• urlchecker::url_check()
• devtools::check(remote = TRUE, manual = TRUE)
• devtools::check_win_devel()
• rhub::check_for_cran()
• urlchecker::url_check()
• git push
• Draft blog post

Submit to CRAN:

• usethis::use_version('minor')
• devtools::submit_cran()
• Approve email

Wait for CRAN...

• Accepted 🎉
• git push
• usethis::use_github_release()
• usethis::use_dev_version()
• git push
• Finish blog post
• Tweet
• Add link to blog post in pkgdown news menu

Custom error message for missing acces key and app id

Prevent "Add" action from using the `_RowNumber` and `Row ID` columns