CEDAR Template Builder for Google Sheets

An app tool to streamline the creation and management of CEDAR metadata templates that works seamlessly with Google Sheets.

Installation

This step-by-step instruction will guide you to install the app tool from its source code to your own spreadsheet in Google Sheets.

1. Install clasp.

   npm install -g @google/clasp
   

2. Enable the Google Apps Script API by visiting https://script.google.com/home/usersettings.

   

3. Login to your Google Drive account to enable the CLI commands.

   clasp login
   

4. Clone this project to your local machine.

   git clone https://github.com/metadatacenter/cedar-template-builder-for-gsheet.git
   

5. Create a new spreadsheet file in your project folder in Google Drive.

   

6. In your spreadsheet, go to Extensions > Apps Script to open the script editor.

   

7. In the Apps Script editor, go to Project Settings from the left-hand menu bar and copy the Script ID using the "Copy" button.

   

8. Navigate to the project folder and link it to your remote Apps Script project using the Script ID.

   $ cd cedar-template-builder-for-gsheet
   $ clasp clone [PASTE SCRIPT ID HERE] --rootDir `pwd`
   

9. Push the files to the Apps Script project.

   clasp push
   

10. Refresh the spreadsheet to activate the code.

    

Initial Setup

Follow this instruction to set up the environment variables and initialize the tool for the first time.

1. Select Extensions > Apps Script and navigate to Project Settings. Scroll down to locate Script Properties.

   

2. Add the following properties and their values.

   Property	Value
   glossary.fields.index_column	2
   glossary.valuesets.index_column	3
   template.index_column	2
   value_selection_limit	100
   value_selection_offset	25

3. Go back to the spreadsheet and select Automation > New metadata specification... from the spreadsheet menu. Resolve any authorization prompts that appear.

   

4. Executing the create a new metadata specification command for the first time will trigger the auto-generation of 3 system-required sheets for storing the fields, value sets and prefixes:

   • .FIELDS: Used to store reusable template fields.
   • .VALUESETS: Used to store the value sets.
   • .PREFIXES: Used to store concept prefixes referenced in value sets.

5. Enter a preferred name for the new metadata specification in the dialog box. This will automatically generate a new sheet where you can define the specification.

6. Share the spreadsheet with the setting "Anyone with the link" and grant at least Viewer access to grant a read access to third-party APIs.

   

Contributing

Follow the workflow below to contribute on the code development of this software.

1. Navigate to the project folder and make a new branch and switch to it.

   git checkout -b feat/xyz
   

2. Write your code using the Apps Script editor in the browser.

3. Download the new code to your local project folder.

   clasp pull
   

4. Create a commit.

   $ git add .
   $ git commit -m "Add a detailed commit message describing your changes."
   

5. Push the branch to GitHub.

   git push --set-upstream origin feat/xyz
   

6. Create the pull request.

   gh pr create --base main --head feat/xyz --title "Your PR title" --body "Detailed description of the changes"
   

7. Or alternatively, you can visit the project GitHub page from the browser to create the pull request interactively.