---
tags: Operations
---

# Becoming a populator-expert

Oftentimes, organizations that want to use Alkemio already have a bunch of data. To transfer all this knowledge to the platform, you can use the populator. It's very easy: you place all information on the right location in the populator sheet, run the populator and voilá. There are a few things that will make your life much easier when using the populator, mainly if it concerns large amounts of data. Here's a brief tutorial on how to prepare and execute the population effectively.

## Step 1: Preparing the Excel file

### 1. Creative process
The data that the customer has is very probably differently organized than how we do it on the platform. Therefore, it requires some creative thinking on how to best capture all the data on Alkemio. Make a list of all the data types that the customer has (e.g. projects, locations, people involved, etc.) and for each data type, decide where this information will go (e.g. opportunities, city, lead users).

### 2. Populating the sheet
After you've decided what information must go where, it's time to copy the data from the customer and place it in our [populator-sheet template](https://stichtingcherrytwist.sharepoint.com/:x:/s/Contributors/EbS8hxN_ZCBJqNeac-LDIUsB5Hxp0ZhiS9zwR0wQ0b_ZAw?e=VHnfqr). It has been prefilled with UN SDG information, so make sure to empty it first.

There are a few requirements to the sheet, which are:
- Hubs, Challenges, Opportunities should have a NameID.
- NameIDs should be lower case, have no spaces and max 25 characters
- In the case of a Challenge/Opportunity, you should state the NameID of it's parent (The Hub or Challenge above it)
- If the option is given to add a visual, this field should be filled in. Use an image URL.
- A Hub should have a host. Fill in the NameID of the facilitating organization.
- A Challenge needs a lead user, fill in the nameID of a user, which can be found through a query or in the URL when visiting someone's profile on the platform. You can also create new users and define the NameID yourself.

You can both create a new Hub, or add data to an existing Hub. If updating an existing Hub, specify the Hub using the NameID. Only the fields that you fill out will overwrite existing data.

### 3. Tips and tricks

The tips and tricks stated below will make your life a lot easier!

#### Combining Excel cells
Let's say the customer's data is spread out over multiple columns in Excel but you want to combine it, as it should all go into (e.g.) the "background" section of a Challenge. There are two ways of doing this in Excel:
1. Use the "&"-operator
The "&" operator combines the information from the cells you put around it. It directly pastes the content together. If you want to add a space and a comma, you should also include ", ": `=A1&", "&A2`. It is important to put text that you manually add between quotation marks.

:::success
:::spoiler click to see screenshot
:::

<br/>

2. Use the TEXTJOIN function
If you have to combine a large amount of cells and you don't want to put &", "& between each cell to delimit them, you can also use the TEXTJOIN function, where you select one delimtiter for all cells, you select whether to exclude empty cells (TRUE or FALSE), and then you select the cells that you want to combine.

:::success
:::spoiler click to see screenshot
:::
<br/>

#### Converting names to nameIDs
For many mutations, you need to add a nameID, which has a character limit and the requirement that it should be all lower case characters, not separated by a space and there is a character limit of 25. To easily create a nameID, follow these steps:

Convert the name to small caps, replace the spaces by a dash and truncate to at most 24 characters by using this formula:`=LEFT(LOWER(SUBSTITUTE(A2;" ";"-"));24)`
The SUBSTITUTE function substitutes all spaces in a string by a dash, the LOWER function makes all caps letters lower case, the LEFT function truncates the string of text to 24 characters, starting on the left hand side of the string.

::: warning
Note! It better to have more appealing or content related nameIDs. This is a quick and dirty way. If you have time, check them and make them a bit neater.
:::

:::success
:::spoiler click to see a screenshot
:::

#### Other Excel functionalities to look into
- Pivot tables --> might help you structure data
- Data/Remove duplicates --> if you have a large list with e.g. orgs, and only want each org once, with which you can create new orgs in the Organizations tab of the populator sheet. Use the name-ID convertion trick to quickly create nameIDs for the orgs!

#### Text formatting
You can upload formatted text according to Markdown. When you want text to appear in *italic*, put one asterisk at each side of the text (no spaces): `*example*`. For **bold**, add two at each side. Check this [cheat sheet](https://www.markdownguide.org/cheat-sheet/) for more options.

Especially with large pieces of text, you might want to have a vertical break every now and then. This can be achieved by putting in `<br/>`. If putting together larger pieces of text using `TEXTJOIN`, you could set `<br/>` as delimiter.

:::success
:::spoiler click to see a screenshot
:::
<br/>

Hyperlinking works as follows: the text that must become blue and clickable should be beteween square brackets and the link between parentheses `[blue text](https://www.google.com)` --> [blue text](https://www.google.com).

#### Automated search for images using Google
As stated before, a lot of entries in the populator sheet require an image. When you want to populate for example 25 challenges, it will take you a long time to look for 25 images. You can use a script in Google Sheets that lets you retrieve the image URL based on keywords.

Here's a video tutorial on how to set up and use this script:

:::success
:::spoiler click to see a tutorial video
<div style="padding:75% 0 0 0;position:relative;"><iframe src="https://player.vimeo.com/video/781504944?h=b4ba544907&amp;badge=0&amp;autopause=0&amp;player_id=0&amp;app_id=58479" frameborder="0" allow="autoplay; fullscreen; picture-in-picture" allowfullscreen style="position:absolute;top:0;left:0;width:100%;height:100%;" title="Video_tutorial.mp4"></iframe></div>
:::

You need a Google account (e.g. gmail) to do this. There is a limit of 100 searches / day! If you want to do more, you should create another setup using a different Google account.

Most of the times, the results are of poor quality and the images found might be licenced. Therefore, be very careful with the use of this functionality. It serves well for demonstration purposes.

## Step 2: Running the populator
Now that you've filled in the Excel sheet, it's time to populate it using Visual Studio Code.

### 1. Make sure you're VSC is set-up to populate
You're VSC should be set-up properly to allow you to do the population. Ask Neil or someone from development to help you set it up.

### 2. Save all files in the right folder
Now that you're all set up, you will have a copy of the 'populator' folder locally on your computer.

1. Make sure the Excel sheet has been saved as an .ods file in this folder
2. You need to put in the right data in the .env file. What you need to change is:
- The `API_ENDPPOINT_PRIVATE_GRAPHQL` and `AUTH_ORY_KRATOS_PUBLIC_BASE_URL`. If populating on the production environment, both urls should start with "https://alkem.io/...". If populating on demo, they should start with: "https://demo.alkem.io/"
- Make sure the right file is populated. Fill out the name of the ods Excel file at `ALKEMIO_DATA_TEMPLATE`, e.g. "newhub.ods".
- Enter your email address under `AUTH_ADMIN_EMAIL`
- Enter your password under `AUTH_ADMIN_PASSWORD`
- If you want to create a new hub, set `ALLOW_HUB_CREATION` as "TRUE"
- Save the .env file
3. Before you continue, it is very important to double check and save the .env file (`file/save`).
4. When both the .env file and the .ods file are saved in your local population folder, it's time to start the population!

### 3. Start populating
1. In the top, go to `Terminal/New Terminal`.
2. You will see: `[Folder name]\populator>`
3. Fill in: `git pull`, hit enter
4. Fill in: `npm install`, hit enter
5. Fill in: `npm run populate`, hit enter
6. The population has started!! If everything goes well, you'll see the (updated) Hub on the platform very soon. In your terminal, you might see some warning messages. Read them carefully, as they might indicate a mistake in your .ods file.

:::success
:::spoiler click to see a screenshot
:::