This ticket involves the implementation. After releasing we'll enter the necessary local sheets into each site, and switch them to use Google instead of Smartsheet.

Context

As a consequence of the transition away from Smartsheet, the Global Programme Office's has implemented a new tool to manage global campaign projects: PitchHub. To make sure our standards are integrated with the new programme system, we have migrated the SSOT for Global Project Standards to Google Sheets. 

Obs: The license we use to integrate the Smartsheet API with Planet 4 will be revoked on December 23rd. 

Task

1. Investigate how to read values from a Google Sheet:
   1. using a GCP service account (see the documentation of Burlo's solution here)
   2. using an API key**
2. Read values from the new "SSOT Global Campaign Projects" sheet
3. Make sure existing functionalities are kept on the migration:
   1. Values from the "Global Project Standards" (column B) should feed in the "Global Project" analytics dropdown
   2. Filtering: Sync only the "Global Project Standards" values which have the "Standard Approved" value as true (column F)
   3. Order the values in alphabetical order
   4. Fetch values from the sheet every time when a new campaign page is created. Cache only for 5 minutes.
   5. The selected Global Project value in the dropdown should fill in the 'gCampaign' dataLayer variable, and the respective Project ID (column E) should fill in the 'projectID' variable
   6. Make sure existing selection/data on Planet 4 pages/posts are kept after listing us updated. If an existing value is removed (or unapproved) from the SSOT sheet it should remain on the page (dataLayer value must not change). In this case, we should add a label [deprecated] on the dropdown value in the backend. 
4. Apply new changes to the setup:
   1. Remove the gScope value from the dataLayer. We are deprecating this custom dimension. 

User Stories:

• As a data analyst I want to display the Global Projects Standards values on the Global Project (dataLayer) analytics dropdown fields so that editors can properly track their content
• As a data analyst I want to display only the approved Global Projects Standards so that we can have an extra layer of control before synchronising a value
• As an editor I want to view the Global Projects Standards values in alphabetical order so that I can easily find a specific project when creating a new content. 

Important notes:

We need to coordinate when the ticket goes live to production, to make sure the changes are happening in Burlo at the same time. After UAT, please let jmarubay and gabor.galgocz know when we're ready to go live.