R code for creating a new form in Central from xls/xlsx using API

I'm trying to upload XLSForm (xls/xlsx) to the ODK Central from R without success.

I'm using ODK Central v07 installed in DigitalOcean.

I'm able to upload xml file with following code

  username <- 'myusername'
  password <- 'mypassword'

  xmlfile <- "test.xml"

  ## POST form in xml format to Central
  httr::POST(url,
             httr::add_headers(
               "Content-Type" = "application/xml"
             ),
             httr::authenticate(username, password ),
             body = list(x = httr::upload_file(xmlfile))
  )

However, the similar code is not working with xls file

  username <- 'myusername'
  password <- 'mypassword'

  xlsfile <- "test.xls"

  ## POST form in xls format to Central
  httr::POST(url,
             httr::add_headers(
               "Content-Type" = "application/vnd.ms-excel"
             ),
             httr::authenticate(username, password ),
             body = list(x = httr::upload_file(xlsfile))
  )

xlsx file also not working:

  username <- 'myusername'
  password <- 'mypassword'

  xlsxfile <- "test.xlsx"
  ## POST form in xlsx format to Central
  httr::POST(url,
            httr::add_headers(
              "Content-Type" = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            ),
            httr::authenticate(username, password ),
            body = list(x = httr::upload_file(xlsxfile))
  )

Even tried to upload xlsb (binary format)

These are the files
test.xml (922 Bytes) test.xlsx (9.7 KB) test.xls (26 KB) test-xlsb.zip (6.2 KB)

Is there anything I'm missing?

Hi @Odil!

For XLSForms, you also need to specify an X-XlsForm-FormId-Fallback header. This tells ODK Central what to use for the form ID if no ID is specified in the XLSForm. I believe the header is required even if you know that the XLSForm specifies a form ID.

Hope this helps!

Hello @Matthew_White!
Thank you for your response. I tried with X-XlsForm-FormId-Fallback too, with no success.

Another note: According to the API documentation

You must also provide an X-XlsForm-FormId-Fallback request header with the formId you want the resulting form to have, if the spreadsheet does not already specify. (source)

So I decided that I should specify formID, such as "xmls_form", but in the examples it indicates file name such as "filename.xlsx"

HEADERS
Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
X-XlsForm-FormId-Fallback:filename.xlsx

I tested both: file name as well as formID, but still having Error 400:

Response [https://sandbox.central.opendatakit.org/v1/projects/12/forms?ignoreWarnings=true]
  Date: 2020-01-03 06:42
  Status: 400
  Content-Type: application/json; charset=utf-8
  Size: 90 B

This is the reproduction code for ODK sandbox with file:

  url <- "https://sandbox.central.opendatakit.org/v1/projects/12/forms"
  username <- USER_NAME
  password <- YOUR_PASS

  ## you might want to give absolute link to the file
  xlsxfile <- "test.xlsx"
  
  ## POST form in xml format to Central
  httr::POST(url,
             httr::add_headers(
              "Content-Type" = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
              "X-XlsForm-FormId-Fallback" = "test.xlsx"
             ),
             httr::authenticate(username, password),
             body = list(x = httr::upload_file(xlsxfile ))
  )

test.xlsx (9.7 KB)

My knowledge of R is limited, but I was able to upload your form to the sandbox using curl:

curl -X POST -H 'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' -H 'X-XlsForm-FormId-Fallback: test.xlsx' -H 'Authorization: Bearer [TOKEN]' --data-binary @test.xlsx https://sandbox.central.opendatakit.org/v1/projects/1/forms

This makes me think that the issue could be something specific to R or httr. Are you able to upload the form successfully using curl?

Just as a wild guess, I see that httr::upload_file() accepts an optional type argument. I know you're specifying a Content-Type header already, but do you also need to specify a type to upload_file()?

Another thing that would be helpful would be to look at the body of the error response, which should be a JSON object with more details about the error.

You're actually allowed to specify any valid form ID as the form ID fallback. Either the filename or something else should be fine (though if you specify the filename, you should probably remove the file extension). The form ID fallback request header should specify what form ID to use if the XLSForm does not specify one, but it's flexible about what that fallback is. The example specifies the filename just as an example, because pyxform uses the XLSForm filename as the form ID if the form does not specify one.

In your case, your XLSForm specifies form_id in its settings sheet, so the form ID fallback won't be used. I believe you still need to specify something (that is, the header is still required), but it's not as important what it is if you know it won't be used. (At some point, we should probably make the fallback optional in such cases.) In other words, I think what you're specifying for this header should be OK, so the source of the error is likely somewhere else.

1 Like

Thanks @Matthew_White for the hint with content_type in httr!
Here is the working code in R to upload the excel file to the Central server:

centralupload <- function(){

  ## Change following to you own values
  url <- "https://[CENTRAL_SERVER]/v1/projects/[PROJECT_ID]/forms"
  username <- 'myusername'
  password <- 'mypassword'

  ## you might want to give absolute link to the file
  xlsfile <- "test.xls"

  ## POST form in xls format to Central
  ## Construct the body 
  mybody <- httr::upload_file(xlsfile, type = "application/vnd.ms-excel") 

  ## Upload the file
  ## Note that  header "X-XlsForm-FormId-Fallback"  must be included, 
  ## but if you have form ID in the excel file the argument you are sending 
  ## with this header will be ignored.
  httr::POST(url,
             httr::add_headers(
                "X-XlsForm-FormId-Fallback" = "test_survey"
             ),
             httr::authenticate(username, password ),
             body = mybody
  )
  ## Use:
  ## type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
  ## to upload xlsx file
}

EDIT: I added url object.

2 Likes

Great thanks for the function @Matthew_White

I noticed in the post above that the vector applied for the "url" object was missing, so it may not have worked.

Sham

1 Like

Thanks, @Lal_S, I edited function above - added url object.

3 Likes

Hi,

Great bit of code which will save me laods of time. A couple of things we have forms in spanish and english, when adding the xlsx file we get presented with the warning 'language declarations do not contain valid machine-readable codes' whihc prevents it from uploading via R - is there a way around this? Secondly, once a form is uploaded can we alos add media files via POST, i gave it a go but had no luck.

Cheers,

Stu

@Stuart, Re Warning: I think if you use column names for labels as: "label::English (en)" and "label::Spanish (es)" instead of just "label::English" and "label::Spanish" this can solve your problem.

Regarding uploading a media files using API in R: I didn't try it yet, but I can publish it here when I have a working code.

All the best,
Odil

1 Like

@Odil work like a charm - thanks

Has anyone been having issues with using this since the introduction of draft versions?

The following is my latest code to upload xlsx file into ODK Central:

centralupload <- function(){
  library(glue)

  ## Assign values to variables  
  server <- 'myServerURL'
  pid <- 'myProjectId'
  url <- glue('{server}/v1/projects/{pid}/forms?ignoreWarnings=true')

  username <- 'myusername'
  password <- 'mypassword'

  ## File location, you might want to give absolute link to the file
  xlsxfile <- 'test.xlsx'

  ## POST form in xlsx format to Central
  ## Construct the body 
  mybody <- httr::upload_file(xlsxfile, type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
  
  ## Upload the file
  ## Note that  header "X-XlsForm-FormId-Fallback"  must be included, 
  ## but if you have form ID in the excel file the argument you are sending 
  ## with this header will be ignored.
  r <- httr::POST(url,
                  httr::add_headers('X-XlsForm-FormId-Fallback' = 'test_survey'),
                  httr::authenticate(username, password),
                  body = mybody
  )
  
  httr::http_status(r) -> publish.status
  httr::content(r, "parsed")  -> publish.data

}

This will upload form into ODK Central as draft, and one more step is needed to publish it:

publishdraft <- function(){
  library(glue)

  ## Assign values to variables
  server <- 'myServerURL'
  pid <- 'myProjectId'
  formid <- 'myformid'
  version <- '1' # you might want to increase it when uploading a new versions

  username <- 'myusername'
  password <- 'mypassword'

  url <- glue("{server}/v1/projects/{pid}/forms/{formid}/draft/publish?version={version}")
  
  ## Publish draft form in  Central server
  r <- httr::POST(url, httr::authenticate(username, password))
  
  httr::http_status(r) -> publish.status
  httr::content(r, "parsed")  -> publish.data
}

Hope this helps.

1 Like

@Odil Works perfectly, thanks for the code and the speedy reply