Google Sheets and Asana API exploration for new WCS work packages

[daissatou2]

Background, context, and business value

WCS is looking to expand the existing Kobo -> Asana workflow where OpenFn currently fetches Kobo form submissions and creates Asana tasks for each one. With the new changes, after creating Asana tasks,

1. OpenFn will sync the Kobo form data to Google Sheets
2. WCS users will update specific columns in the rows created by OpenFn with relevant information
3. OpenFn will sync the information added by users in step 2 to the original Asana task that was created as Asana comments

See the new workflow diagram below. Questions in yellow are for Diane and questions in pink are for our team to explore and detailed below.

The specific request, in as few words as possible

Explore the Google Sheets and Asana API documentation and test if it is possible to complete the scenarios below. Use your own Google Sheet and your OpenFn Asana account to test.

1. Edit one cell in the Google Sheet on the front end. Is it possible to fetch only rows recently updated via the Google Sheet API? I.e can you tell from the API response that only the cell you changed contains updated data?
2. Is it possible to create a new comment on an existing Asana task via the API? (Without overwriting anything else about the task).
3. Is it possible to update an existing comment on an existing Asana task via the API? (Without overwriting anything else about the task).

adaptor

Google Sheets
Asana

[daissatou2]

@mtuchi I have scheduled time for us to walk through this tomorrow morning.

[mtuchi]

Asana 🔄 GoogleSheet two way Sync

Hey @daissatou2 , This is a feedback based on a research i have done for achieving two way data sync between Asana & GoogelSheet

GoogleSheet

• We can update data in spreedsheet using batchUpdateValues API [Will add this function in GoogleSheet Adaptor]
  • Must know the cell position or sheet range
• We can sync data from GoogleSheet to OpenFn using Google Apps Script

// Function to create a custom menu
function onOpen() {
 var ui = SpreadsheetApp.getUi();
 ui.createMenu('OpenFn Sync 🔄')
   .addItem('Sync', 'syncChangesToAPI')
   .addToUi();
}

function syncChangesToAPI() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var range = sheet.getDataRange();
 var values = range.getValues();

 // Convert arrays of values, comments, and locations to a JSON object
 var data = {
   values: values,
 };

 // Make a POST request to the REST API URL
 var apiUrl = 'OPENFN_WEBHOOK_URL'; // Replace with your API endpoint URL
 var options = {
   method: 'post',
   contentType: 'application/json',
   payload: JSON.stringify(data)
 };

 try {
   var response = UrlFetchApp.fetch(apiUrl, options);
   Logger.log('Data synced successfully:', response.getContentText());
 } catch (error) {
   Logger.log('Error syncing data:', error);
 }
}

Important Notes for Google Sheet

Those two approach above should allow us to update data in spreadsheet and get update data on googlesheet to OpenFn where we can map & transform to Asana data model.

⚠️ We can not fetch comments from Google Sheet

Asana Not Tested

• Fetch comments

 GET /tasks/{task_gid}/stories
 Then, filter the result with type = “comment”.

API Reference

[mtuchi]

Hiya @daissatou2 there is a work in progress for fetching latest changes from Google Sheet #205 , And i have also made some improvements on Asana adaptor that will allow me to create a comment for a given task OpenFn/adaptors#495. I will finish up the remaining part tomorrow