This Google Sheet file is mainly for managing Watchlist and supporting Researchers. It is a miniature version of and Defillama helps you quickly grasp information on many projects in a single interface. **» File: (https://docs.google.com/spreadsheets/d/1PHbDLboND2wz2MuG4KKi6hSkP7L0IvDQJKool8IiYr4/edit?usp=sharing)(https://docs.google.com/spreadsheets/d/1PHbDLboND2wz2MuG4YKi40hSsharingolD7JL0hSv )** ## **INITIAL SETUP AND USAGE INSTRUCTIONS ** **Initial setup steps:** 1. Sign up for a Coinmarketcap API account 1. Clone file for private use 1. Create Trigger automatically update data 1. Add “token slug” and start using ## STEP 1: REGISTER ACCOUNT COINMARKETCAP API File will automatically update data from 2 sites, Coinmarketcap and Defillama. Defillama for getting data without registration should be ignored, Coinmarketcap requires API KEY so you need to register for a FREE account. 1. Access Coinmarketcap API website here: (https://coinmarketcap.com/api/)(https://coinmarketcap.com/api/) 1. Click **Get Your API Key Now** button and Follow the instructions to sign up for an account. During registration, in the Plan section, just select Basic – Free basic access for personal use. Go to the email you used to register and press Verify to complete. 1. After the registration is complete, you will see the Account interface. Move the mouse to the API Key box and the Copy button will appear, Copy this API Key to use in the next step. Also you see, Coinmarketcap is limited to 333 data retrievals a day and 10,000 times a month. Once F5, there were 2 requests sent to Coinmarketcap to get data, so a day should only F5 about 150 times, more than not being able to update the data anymore, but have to wait until the next day * (maybe one day you guys too) Don’t F5 Watchlist more than 100 times* ) ## STEP 2: CLONE FILE WATCHLIST TEMPLATE Go back to the Google Sheet file: Watchlist Manager Template that I shared to clone the file. 1. On the Google Sheet toolbar, click **File > Make a copy** 2. A Popup will appear for you to choose where to save the file on your Google Drive, you can also rename the file to be stylish: D. > *In the Popup the message also shows: Clone file will also clone the executable Scripts of the file, you don’t need to worry, because all the source code I wrote inside is **OPEN SOURCE**, anyone You can also view and know what operations the file does, without collecting any information. You can check the source code script by selecting Extension > Script after you have cloned the file.* 3. After Clone the file, remember to Bookmark it again for easy access next time. ## STEP 3: CREATE AUTO UPDATE DATA TRIGGER The data updater code from Coinmarketcap and Defillama won’t run if you don’t press the run button in the Script driver. But Google has a tool to help you execute it automatically every time you open the file or press F5. It’s **Triggers** from Google itself. To do this, do the following: 1. At the interface of the Sheet file that you have cloned *(your file).* 2. On the Menu toolbar click **Extension >** Select **Apps Script ** 3. In the Apps Script interface, notice the menu along the left hand side will see a clock. Move the mouse to and click on it – **Triggers** 4. At the Triggers interface, look down at the bottom right corner to see the button: **Add Trigger**, click it. 5. Popup setup Trigger appears, set up as shown below. 6. Click Save, wait a moment. 7. Another Popup will appear for you to grant permission for the Script to run, if not, press **Save** again, or check if the Popup is blocked by Google Chrome. 8. At Popup grant permission press **Advanced** 9. Then click Go to **Watchlist Manager (unsafe)** 10. In the next window scroll down to the bottom select **Allow** Trigger setup is complete , from now on every time you open the file or press F5, the data will be reloaded from Coinmarketcap and Defillama. **Note:** If you don’t understand programming, don’t edit miscellaneous code, just set up according to the instructions, and if you know the code, you can customize the App to suit your needs. ## STEP 4: ADD SLUG TOKENS AND START USE Important: First you need to know what “slug” is? “Slug” is the end of the link to the Coinmarketcap token information page.Actually it is possible to get the data through the token, but on coinmarketcap there are many projects with the same token, this can lead to download information. So the best way is to follow the slug – the link to coinmarketcap’s token page.Look at this picture below, you will understand what a “token slug” is.The project slug in the picture is the text at the end. between the two signs / …. / = **ethereum** When you need to put Ethereum in the watchlist, copy the word “**ethereum**” and paste it into the Slug column in your file * (remember to remove the slash, I only use the word ethereum)* **After the SLUG has been cleared, YOU NEED TO KNOW THE STRUCTURE OF THE FILE AND THE REMOVABLE AREAS, INSTANT REGULATIONS.** **File includes 4 Sheet ** 1. Sheet 1 – Guide: Introduction. After reading it, you can hide or delete it. 1. Sheet 2 – Tracking: The main work area here, there are some self-updating data areas, there are some manually updated areas. This Sheet cannot be renamed. 1. Sheet 3 – Portfolio: Data is filled in completely automatically, this Sheet only needs to be viewed. This Sheet cannot be renamed. 1. Sheet 4 – DB: Where to enter your COINMARKETCAP API, just fill it in once and then hide it but **cannot be deleted, cannot be renamed.** First, go to **Sheet 4 – DB ** before. Copy the Coinmarketcap API obtained in step 1, paste it into **Cell A2** (just below the COINMARKETCAP_API header box). Note, paste this box correctly. Done with the API update! **Now focus on Sheet 2 – Tracking.** First, I will explain about 3 columns **ABC**, used to divide categories. When you enter an asterisk “ * ” into any cell in columns A or B or C, the whole line will light up in different colors. With column A is orange yellow – represents for large titles. Column B is dark gray – represents the Crypto category at your disposal. Column C is light yellow – representing the project you need more focused highlighting. > *This section is set up with the available “Condiional Format” of Google Sheet, if you are fluent, you can edit the colors as you like.* If you want to add a certain category, just enter a name for it in the column. D and add a ” * ” in column B along the same line to color it. *Note, once you have entered it as a category in column D, you cannot enter other information, the list is only on one line. The projects in the list will be imported from the line below.* **The second very important column you need to pay attention to is column F – Slug.** This is where you enter the Slugs of the project you want to track. . Just paste the slug of the project to be tracked into this column, the same can be found in many categories, but **definitely not wrong**, **wrong 1 slug is the whole code not running**. This is the most common error, if you see an error message in Cell A1, the first thing to think about is to check if column F has any wrong slugs. Once the Slug has been correctly filled in column F, after F5, columns D, E and H to AE will be automatically updated, you do not need to fill in any data in these columns. **Next is column G – Portfolio** Column G is a checkbox. This column means to say which projects are in your portfolio, click checked, for easy tracking. The second purpose is for the script to identify and update to a separate 3rd Sheet – Portfolio. Sheet Portfolio is a 100% auto-updating sheet, you don’t need to customize anything. This sheet will take all the projects that you ticked in column G – Portfolio of Sheet 2 – Tracking to. The purpose is to categorize you to focus on the projects you have invested in. **Still in Sheet 2 – Tracking.** Areas from behind column AE onwards *(after gray line)* are hand input data, you are free to modify structure and enter notes anything whatever you want without fear of overwriting data every time you update. Some API websites don’t support it, so they don’t have built-in links to click one to go to the information page like Messari, Dune, Chainbroker, so I converted it into a Google Search link to its site. **Cell A1** of Sheet is where the update status of the file is displayed. If displayed Updated is successful data update. If there is an error, it will report Error with the same error content. Errors are usually caused by entering the wrong **slug** in column F. Also, feel free to play around, fix, break this and that, when the error doesn’t work, go back to the file you shared and clone again to redo the word. head. > **I remind again that this entire Google Sheet file and the script code inside are OPEN SOURCE, anyone can see what the file does, so I will not bear any responsibility. No responsibility for your data.** Finish setup! **If you find it useful, you can give me a heart and share!** **Thank you **
Source: Collector
