Update GoogleSheets workflow to work with multiple country offices

[daissatou2]

Background, context, and business value

WCS hopes to replicate the Aches Kobo/GoogleSheets/Asana setup across multiple sites. This means each site will have its own: Kobo form, Google Sheet and Asana Project.

The specific request, in as few words as possible

Update the existing Kobo to GoogleSheets to Asana workflow currently running on V1.
See updated steps in red in the workflow diagram.

Upsert to Asana job updates:

Update all of the upsert to Asana jobs listed below to send a message to the inbox after the Asana upsert is successful. The message should include the original data from Kobo and the Asana task ID. {TO-DO: list all jobs below}

Sync to GoogleSheets job (currently named GRM02.b Sync to GoogleSheets Aceh) updates

1. Rename the job to something more generic like "Sync to GoogleSheets"
2. This is currently a flow job. Make it a message filter job so it can be triggered by any of the GRM forms being synced to Asana.
3. Add a step at the top of this job to query this master sheet and return the "Google Sheet Link" (column Y) where "Kobo account used" (column P) is equal to state.formOwner.
4. Upsert the correct GoogleSheet using the link returned in step 3

No changes need to be made to the app script or the update asana task job since the project id is not needed to update a task in Anna

Credentials

Kobo:
configuration: see LP OpenFn Kobo Toolbox

Googlesheets
For now use your own Google Account - you have access to this sheet

Asana
For now use your own Asana Account - you have access to this project

adaptors

[email protected]
[email protected]

Workflow/Job

https://github.com/WCS-ConsTech/OpenFn-GrievanceRedressMechanism/blob/master/automation/syncGsheet.js
See here to see this^ job in platform v1

You can search jobs for GRM02 to see all of the jobs in this workflow.

Testing Guidance

See test suite we can walk you through.

Toggl

WCS Support

[aleksa-krolls]

hey @daissatou2 you have yet to fill in the job links, yeah?
Then a couple of questions/feedback for you>

1. Re: your comment "No changes need to be made to the app script or the update asana task job since the project id is not needed to update a task in Asana" - so right now this is true. I think this is because we assume all edits to this Google Sheet should flow to the 1 Aceh Asana project. However, if we have multiple copies of this GoogleSheet live, won't we need to know which changes belong to which Asana project? I don't think Asana Task Id is enough, right? We might need Asana Project AND Task Id, no?
2. Re: your comment below - I think all the WCS sites will be rolled out to will also be in Indonesia. I'll 100% confirm tomorrow. If that's the case and all in Indonesian, then do we need to worry about your below comment? Or all good?

The Gsheet column names are referenced in the [updatesheetask](https://github.com/WCS-ConsTech/OpenFn-GrievanceRedressMechanism/blob/master/asana/updateSheetTask.js) job. These names are currently in Indonesian. I'll need to work with Mtuchi to analyze all the jobs and figure out how to make them independent of the column names (maybe we can leverage column order instead) or maybe we need another master sheet to translate the column headers.

3. Do you have a link to past test scenarios you used in the original implementation we can use? Thanks!

[daissatou2]

@aleksa-krolls

1. We do not specify a project ID when updating a task. See Asana API Docs: https://developers.asana.com/reference/updatetask
2. Then we should be all good!
3. Test suite
4. I have added certain jobs links. I did not list the "Upsert to Asana" jobs because I'm not sure which upsert to Asana jobs this will apply to...and you mentioned that these will be new sites in Indonesia-do these already have existing upsert to Asana jobs?