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.

You will need to edit the Sync to GoogleSheets job (currently named GRM02.b Sync to GoogleSheets Aceh in v1) as follows:

1. Query this master grm-DEPLOYED sheet to get mappings for the "source" kobo formuid (column B) and destination Google Sheet Link (column O).

• NOTE: Only return rows where Google Sheet Link is defined, because not all Kobo form uids will have a matching target spreadsheet.

2. This should output a mapping table that looks like this and defines routing rules from syncing Kobo data to different GoogleSheets:

const formSheetMap = {
  \\ uid: sheetId, 
  'aPY82ZadEVsiZLZCwAY4Ka': uTQRVKggUsj6wycRhBUU9ywixsOniBbEa0J8e9ruHHI,
  'a2SXkrerDvs2ZnwjaVKTYg': '1YKx81FDqK2hY9AAnwF1KjmqEDcIe-ymKVtMncbqjZOU'
}

Note that you'll need to parse the sheetId from the Google Sheet Link (e.g., https://docs.google.com/spreadsheets/d/1uTQRVKggUsj6wycRhBUU9ywixsOniBbEa0J8e9ruHHI/edit)

3. Then based on the kobo form uid in the Input, map the data to the correct Google sheetId using this mapping table.

For the test cases described below, this means:

• Input1 should result in 1 row added to this sheet (the first mapping rule)
• Input2 should result in 1 row added to this other sheet (the second mapping rule).

⚠️NOTE BEFORE STARTING WORK⚠️

• Work on a new branch based on master --> Do NOT merge to prod!
• Job source code lives on this repo OpenFn-GrievanceRedressMechanism owned by WCS's org so it doesn't show up in our Zenhub view, which is why we logged this issue on this repo

Workflow/Job

The workflow has 2 steps. (Search the v1 project for GRM02 to see this.)

Step 1

• Name: GRM02.a upsert Aceh Grievance in Asana
• Job: https://github.com/WCS-ConsTech/OpenFn-GrievanceRedressMechanism/blob/master/asana/upsertTask_Aceh.js
• Adaptor: asana@3.2.0
• Input: See below links to payloads

Step 2

• Name: ``GRM02.b` syncGsheet
• Job: https://github.com/WCS-ConsTech/OpenFn-GrievanceRedressMechanism/blob/master/automation/syncGsheet.js
• Adaptor: googlesheets@2.4.0
• Input: See output of Step1

Input & Testing Guidance

To generate an input for step 2 syncGsheet.js, run the workflow (both steps 1 and 2) using the following inputs:

• Input1: https://gist.github.com/aleksa-krolls/20315b08f95e1294a0559e8b0e01e702
• Input2: https://gist.github.com/aleksa-krolls/59de0398756cf7c9bbcd57fe527c3bc3

Once you run these^ through both workflow steps, then you'll find Input1 should map to GoogleSheet1 (the first mapping rule), while Input2 should map to GoogleSheet2.

Credentials

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

Step2: Googlesheets
For now use your own Google Account - you have access to this master sheet with the mapping rules, as well as target sheet1 and target sheet2.

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?

[aleksa-krolls]

hey @hunterachieng given that you haven't started work here yet, this will get rolled into the next Feb 24th sprint
cc @martalovescoffee

[aleksa-krolls]

hey @hunterachieng as discussed, let me work on revising the spec with new input links and sync with Aissatou to clarify the formOwner question. In the meantime, moving this to Blocked - but should get back to you tomorrow AM with the following:

• New test input with test Kobo form
• New test GoogleSheet to post data to
• Master sheet with test data to pull from

[aleksa-krolls]

hey @hunterachieng as discussed, this issue is ready to go. Please re-read the spec again now that I've made edits, and pls don't hesitate to ask any questions.

[hunterachieng]

@aleksa-krolls the PR has been approved and is ready for testing

[hunterachieng]

@aleksa-krolls Do you mind checking out the PR above? It was approved and awaiting testing before merging.