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, we need to find another solution to support NROs in defining Local Project Standards. We will migrate to Google Sheets since that's also the tool used by the Global Programme Office.
See related ticket to Global Projects: PLANET-6441

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

Task

1. Read values from the testing sheet "SSOT Local Campaign Projects - GP Africa" sheet
2. Each Planet 4 instance should be connected with the respective local sheet.
   1. On the current solution, P4 admins were asked to input a Smartsheet sheet ID on the Analytics settings. If possible, we can keep the same solution for Google Sheets. 
3. Make sure existing functionalities are kept on the migration:
   1. Values from the "Local Project Standards" (column B) should feed in the "Local Project" analytics dropdown
   2. Filtering: Sync only the "Local Project Standards" values which have the "Standard Approved" value as true (column D)
   3. Order the values in alphabetical order
   4. Fetch values from the sheet every time when a new page is created. Cache only for 5 minutes.
   5. The selected Local Project value in the dropdown should fill in the 'gLocalProject' dataLayer 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. 1. Make the Local Projects dropdown mandatory on Campaign and Take Action pages 

User Stories:

• As a data analyst I want to sync each P4 instance with their respective Smartsheet table for Local Projects
• As a web admin I want to be able to define my Local Projects values so that I can improve my local tracking and reporting.
• As a web admin I want to display only “approved” values on the Local Projects dropdown field so that we can have an extra layer of control before synchronising a value
• As an editor I want to be able to select a Local Project value when creating a new content (post, page, campaign).
• As an editor I want to view the Local Projects 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 because we need to be careful not to affect existing data from NROs websites. We will provide a sheet with all NROs tables that should be linked to their instances before we go live.