OPENFN sending data TO Google Sheets

[harry....@faststream.civilservice.gov.uk]

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 :)

[Taylor Downs]

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

--
You received this message because you are subscribed to the Google Groups "OpenFn Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openfn+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/openfn/c74ba351-6668-416f-93cc-236ccb8894df%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Taylor Downs | Founder & Core Contributor
Open Function | Integrated Systems for Development

[Harry Ingram]

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

--

Harry Ingram

Currently posted to;

Lively Minds

Empowering communities through play.

www.LivelyMinds.org

[Taylor Downs]

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!

[Harry Ingram]

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"

}

To view this discussion on the web visit https://groups.google.com/d/msgid/openfn/CAJ8WvYaspSDKK93dcCer3W6RQskeC8wjSjmjD%2B3VOM42OQmqcg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--

Harry Ingram

Systems Architect 

Lively Minds

Empowering communities in Ghana & Uganda through play

Registered Charity No. 1125512 | PO BOX 72928, London, N13 4TL | www.livelyminds.org | 0207 097 8636,

www.twitter.com/livelyminds

www.facebook.com/livelyminds

[Taylor Downs]

// 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"

  ]

}

[Harry Ingram]

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?

[Taylor Downs]

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 (tay...@openfn.org) or Aleksa (ale...@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

To view this discussion on the web visit https://groups.google.com/d/msgid/openfn/CABFh1gpk78encnzHrhLGxeobPoz-X%2BgYqrO4uZCCitfdeMP4eg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.