While building Glific for the past 2.5 years, one of the problems we face while scaling it is that it is difficult to find a one-size-fits-all solution that would work for everyone. As a result, it is often necessary to tailor the solution to the specific needs of each user. So, while thinking about how the same solution may be generalized for other NGOs as well, we have been working on some custom features with our partner NGOs on a consultancy basis. A few of which are covered in the blogs below.

• Implementation for the AI driven bot that promotes a healthy lifestyle
• Experiments with Frappe as CRM in Glific
• Multiple Profiles: A possible way to increase the chatbot’s reach beyond one person per number

Although it may not be possible to achieve the same level of functionality and usability for all but with a few tweaks we have been successful in extending those custom solutions for others as well.

In our most recent sprint with our NGO partners, it came up in conversation that NGOs extensively rely on Google Sheets for maintaining the content for flows at scale. Although we have done sheet integration in one form or another in Glific through webhooks and some custom development with some partner NGOs like The Apprentice Project, Key Education Foundation, and Digital Green, but hearing partner NGOs thoughts in the sprint, we decided that it would make more sense to integrate Google Sheets into Glific for general use.

Read more about our last Sprint here

Soon after the sprint, we began conceptualizing ways to integrate Google Sheets into Glific in a way that makes it simple to use. As it is crucial to maintain the product’s simplicity since users may be less likely to utilize it if the interface is too complicated or the feature is challenging to use.

We broke down Sheet Integration into three parts

1. Conceptualizing solution
   
   • Starting with a use case: We had a discussion with a couple of our NGO partners like Indus Action, Reap Benefit, The Apprentice Project,  Key Education Foundation, and Antarang. This helped us in understanding how they plan the content that needs to be sent from Glific in the upcoming weeks utilizing Google Sheets. We designed a use case for the initial Google Sheets integration based on this understanding, which can then be may be used as a frame of reference while implementing a solution
     
     
   • Designing a high-level solution that fits the use case: With the use case that content needs to be distributed on specific dates that are listed in the first column and the content for each day is in the same row, we created a sample Google sheet which may include a text message in another language, a media link, and other information related to NGO’s program.
     
     
   • Setting up milestones: We created a few milestones prior to starting development where we chose to launch this feature in two stages, with phase 1 concentrating on basic functionality and getting things starting and phase 2 concentrating on various improvements to make it more user-friendly.
     
     
2. Implementing solution
   
   
   • Managing sheets in Glific: As we are planning to integrate sheets in Glific, we decided to have a dedicated screen to manage all google sheets in Glific. This way NGOs can add a new sheet that they intend to use in Glific flows by clicking on Add Sheet button and entering the published link of the sheet. If there have been changes since the last sync, they can also click on the sync button to update the linked sheet.
     
     
     
     
   • Using Sheets in Glific Flows: Once the sheet is added in the Glific, it can be used in the flows using the new node we introduced named Link Google Sheet. It is a simple 4 step process to use the sheet in the flow
     
     1. Add a new node i.e. Link Google Sheet in the flow 
     2. Choose the sheet you want to use in the flow(here it is Daily Activity)
     3. Choose the row key which picks that row from the sheet. Here we added a variable @calendar.day which will pick give the value of today’s date in format DD/MM/YYYY which is the first column(key) in the sheet as well
     4. Add the result name which will save all the values from the row of the sheet. Thus if a column in the sheet named url needs to be used in the flow then and the result name is linked_sheet, then the value from the sheet can be used using @result.linked_sheet.url
        
        
        
        
        
        Once the sheet node is configured we can continue designing the rest of the flow and use sheet data wherever required
        
        
        
        
        
3. Next Steps: It took us around a month to develop this feature, from designing a detailed flow of how the Google Sheet will be integrated into the Glific to developing and making it production ready.
   
   Next, we hosted a webinar where we shared the progress so far with our partner NGOs to hear their thoughts and have a few more ideas that would make the integration more useful
   
   Check out the webinar here