pyBIS and apiV3: how to add and use a spreadsheet

Hello,
I am currently exploring the pyBIS api, and would really like to use the spreadsheet property. But I couldn’t find how to upload some data, or decode an existing one (that looks like eyJoZWFkZXJzIjpbIkEiLCJCIiwiQyIsIkQiLCJF…).
I tried to add some data on my own (like pandas csv to xml), but that only break the entity (it does not shows correctly in the ELN UI), without returning any error.
I also couldn’t find any information on how to do that using the API V3 (that I used before).
Thank you,
Alain

Hi Alain.
Thank you for the question.
One of our colleagues working with the pyBIS will get back to you with the answers.
Just as reminded.
When having some specific, openBIS-related questions, you can drop an email to the openBIS dedicated help desk: openbis-support@id.ethz.ch

Regards.
Ross

Hi Alain,

you can try the following:

import json
import base64
from pandas import DataFrame

# [6:-7] is just a quick and dirty way to get what's inside <DATA> and </DATA>
spreadsheet = json.loads(base64.b64decode(sample.props["general_protocol.spreadsheet"][6:-7]))
df = DataFrame(spreadsheet["data"], columns=spreadsheet["headers"])

To store the (modified) data back to openBIS, you need to export the DataFrame back into the same format:

spreadsheet["data"] = df.to_numpy().tolist()
sample.props["general_protocol.spreadsheet"] = "<DATA>" + str(base64.b64encode(bytes(json.dumps(spreadsheet), encoding='utf-8')), encoding='utf-8') + "</DATA>"

Hope that helps!
Cheers, Swen

Hi Swen,
Thank you for the answer, it really helps. I could make the reading but not yet the writing yet (it upload something but the resulting sample in OpenBis does not show). But that could well be something wrong I am doing (reading from a tsv using pandas as well). I will work on it more later.
Thanks again,
Alain