Hi all,
Was hoping you may be able to help. I am having issues with the code required to send data from OpenFN to a new google sheet.
I am trying to get KoboCollect data to go straight to an updated google sheet to enable easy data visualisation and reduce the need to constantly run reports and export data.
I am not too techie and have, somehow, managed to get my data into OpenFN and now am just trying to create the job to send the data to google sheets but the code I am using from GitHub is creating errors.
Any help would be incredibly appreciated!
Harry 
Hey Harry,
Thanks for reaching out. Would you be willing to share your job expression? The example we’ve got up on Github looks like this:
appendValues({
spreadsheetId: ‘1O-a4_RgPF_p8W3I6b5M9wobA3-CBW8hLClZfUik5sos’,
range: ‘Sheet1!A1:E1’,
values: [
[“From expression”, “$15”, “2”, “3/15/2016”],
[“Really now!”, “$100”, “1”, “3/20/2016”]
]
});
What you’d want to do, presumable, is create that array of values for a single row from your Kobo form submission data. Seeing the structure of that form submission data (in your OpenFn inbox) would also be useful. If you could anonymize it and share it, someone here could help you write the job expression.
Taylor
Thanks Taylor!
Sure, thanks for that. It is the creation of the values that I do not seem to be able to get my head around. It would be great to see an example form/google sheet that fits with your example code in order to be able to see exactly what has been done. I have attached the code from my OpenFN inbox below. This is all example data just to establish a connection between Kobo and Sheets. Thanks so much for your help
OpenFn
Message #3397088
Received on Tuesday, October 2nd 2018, 5:31:03 pm
Change Notes
// Have removed all personal data and authentication key.
Message Body
__query_params:
_tags:
_submission_time
2018-10-02T16:31:02
_geolocation:
0
1
form_id_string
_uuid
69cd5c04-da24-4cb2-bfa9-ea1b9c58f074
formhub/uuid
296d9870a1cd45c3ae4a345b58d59c53
--------_userform_id
ingramh1_----------
_userform_id
ingramh1_------------
Where_are_you_from
uk
_bamboo_dataset_id
uuid
69cd5c04-da24-4cb2-bfa9-ea1b9c58f074
validation_status:
version
vSqx2phrfMqULpTWAccCAi
start
2018-10-02T17:18:10.518+01:00
_notes:
_id
15728992
_status
submitted_via_web
_id
15728992
submitted_by
status
submitted_via_web
_notes:
_geolocation:
0
1
What_is_your_DOB
12018-10-30
end
2018-10-02T17:18:33.687+01:00
_tags:
_submitted_by
submission_time
2018-10-02T16:31:02
bamboo_dataset_id
_validation_status:
_attachments:
_attachments:
meta/instanceID
uuid:69cd5c04-da24-4cb2-bfa9-ea1b9c58f074
How_old_are_you
454345345
Harry
OK, one more quick request: from that message view, can you click the “edit” pencil in the top right one more time so we can see the raw JSON of the Kobo submission? It looks like you’ve pasted in the “form view” from OpenFn’s interface, which can be nice for quick edits, but makes it harder to see what’s really going on with the data.
From this page:
click the pencil in the top right, again so you see this:
And then paste in that raw JSON body so we can figure out how to build Google Sheets values for you!
Thanks Taylor!
Apologies for that. Please see below and thanks so much for your help!
{
“__query_params”: {},
“_tags”: [],
“_submission_time”: “2018-10-02T16:31:02”,
“a----------------------_212738_geolocation”: [
null,
null
],
“a------------------------_212738_xform_id_string”:
“a---------------------”,
“_uuid”: “69cd5c04-da24-4cb2-bfa9-ea1b9c58f074”,
“formhub/uuid”: “296d9870a1cd45c3ae4a345b58d59c53”,
“--------------------212738_userform_id":
"ingramh1-----------------------------------”,
“userform_id": "ingramh1----------------------------”,
“Where_are_you_from”: “uk”,
“_bamboo_dataset_id”: “”,
“-----------------_212738_uuid”: “69cd5c04-da24-4cb2-bfa9-ea1b9c58f074”,
“-----------------------------------212738_validation_status": {},
"-----------------------------------212738__version”: “vSqx2phrfMqULpTWAccCAi”,
“start”: “2018-10-02T17:18:10.518+01:00”,
“_notes”: [],
“------------------------------_212738_id”: 15728992,
“_status”: “submitted_via_web”,
“_id”: 15728992,
“-----------------------------------_212738_submitted_by”: null,
“a----------------------------------_212738_status”: “submitted_via_web”,
“a----------------------------------_212738_notes”: [],
“_geolocation”: [
null,
null
],
“What_is_your_DOB”: “12018-10-30”,
“end”: “2018-10-02T17:18:33.687+01:00”,
“a---------------------------------_212738_tags”: [],
“_submitted_by”: null,
“a--------------------------_212738_submission_time”: “2018-10-02T16:31:02”,
“a------------c_212738_bamboo_dataset_id”: “”,
“_validation_status”: {},
“a------------------------_212738_attachments”: [],
“_attachments”: [],
“a---------------------------_212738meta/instanceID”: “uuid:69cd5c04-da24-4cb2-bfa9-ea1b9c58f074”,
“How_old_are_you”: “454345345”
}
// If this job is executed by a ‘message filter’ trigger, it will get the Kobo
// form submission as its data. You can access values from that submission by
// typing ‘state.data.___________________’
appendValues({
spreadsheetId: ‘1O-a4_RgPF_p8W3I6b5M9wobA3-CBW8hLClZfUik5sos’,
range: ‘Sheet1!A1:E1’,
values: [
state.data.Where_are_you_from, // column A
state.data.What_is_your_DOB, // column B
],
});
// this will output:
{
“range”:“Sheet1!A1:E1”,
“majorDimension”:“ROWS”,
“values”:[
“uk”,
“12018-10-30”
]
}
This is amazing, thanks so much!
It does seem to be returning an error though;
TypeError: Cannot read property ‘accessToken’ of null
at /home/open_fn/priv/language_packs/language-googlesheets-v0.2.1/lib/Adaptor.js:125:43
any ideas as to where this issue stems from?
Hey Harry,
This sounds like the credential you’re using for this job hasn’t quite been set up properly. Are you up to connect on a private thread? Feel free to reach out to me (taylor@openfn.org) or Aleksa (aleksa@openfn.org) and we could set up some time for a proper troubleshoot. If it turns out to be an issue with the language-package we can bring this thread over to Github, or if it’s an usage issue we can post everything here.
Thanks, and sorry for the troubles!
Taylor