Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Google Sheets and Asana API exploration for new WCS work packages #204

Closed
daissatou2 opened this issue Mar 21, 2024 · 3 comments · May be fixed by #205
Closed

Google Sheets and Asana API exploration for new WCS work packages #204

daissatou2 opened this issue Mar 21, 2024 · 3 comments · May be fixed by #205
Assignees
@mtuchi

Comments

@daissatou2
Copy link
Collaborator

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.

image

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
Copy link
Collaborator Author

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

@mtuchi
Copy link
Collaborator

mtuchi commented Mar 25, 2024

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

This was referenced Mar 26, 2024
Merged
Draft
@mtuchi mtuchi linked a pull request Mar 26, 2024 that will close this issue
Google Sheet to Asana Sync #205
Draft
@mtuchi
Copy link
Collaborator

mtuchi commented Mar 26, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mtuchi mtuchi

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Google Sheet to Asana Sync OpenFn/ConSoSci
2 participants
@mtuchi @daissatou2