【Mini Program】Google Sheets | Manage Your Cryptocurrency Wallet Balance

Jan 29, 2022

The last time I used Python to capture the balance of the LikeCoin wallet, it was still a bit difficult to read the comments, and the usage rate should be very low... So I decided to use Google Sheet this week! ! !

The half-baked amateur programmer has come to Lao Wang to sell melons again! ! !

Although there is still code in the background, this time you don't need to copy and paste it, just use the template I provided. All you need to do is: save the template as another, add your wallet address, and follow the steps provided below to set the automatic running time. it is good. I hope this article will not be another " seems to be very powerful, but I can't understand " technical article.

🛑Warning : Because Google Sheets with script will exist in your Google Drive, so if you decide to use it, please also have a Google account without personal data, otherwise you can only confirm the program code yourself, or trust the uncle Goods .

The program code will be pasted at the end of the text for reference.

🟨Introduction to works

This is a wallet balance summary made with Google Sheets, which can help you monitor the three balances (available, entrusted, entrusted income, etc.) in your different wallets, and also provide the corresponding dollar amount according to the latest exchange rate at the time of update. For reference, the current version is limited to coins that have been listed on Osmosis.

The first time you use it, you need to use the computer to set the time period for automatically running the script, and then you can view it on your mobile phone or computer at will.

Click here to open the works.

🟨Applicable crowd

The mobile version of Kepler wallet does not support your coins (like LikeCoin)
I have entrusted several kinds of coins. I want to confirm how much profit I have and I can re-entrust it.
Number of wallets held > 1

Note: If you have several coins but only one Kepler wallet, you can also consider using Ping Wallet for management. For details, please refer to Daisy's article .

The green column is the information provided by the applet

🟨Instructions for use: first time setup

❶ Click here to open the file, click " File " >> " Copy " to enable the copy option .

❷ Change the file name to whatever you like, then select the folder and click "OK". If you feel troublesome, you can click "OK" directly.

Save the template to your own Google Drive, otherwise it cannot be modified

❸ Start updating the copied files. The first row has three colors, please refer to the illustration below. All you need to enter is the blue field, after all I don't know your wallet address, wallet name and currency to look at (laughs).

One line is definitely not enough. To add new ones, just copy the second line and paste it to the third line and repeat the third step.

Please do not change other than the blue column, so as to avoid the failure of the applet

❹ After the data is updated, let's start the script test manually. Click on the "API" workbook and click the "Manual Refresh" button.

This step is to confirm that the macro is functioning properly.

Click Manual Refresh to test if the macro works

❺ You need to authorize the script for the first run, click "Continue", and you will not need to authorize it in the future.

❻ There will also be a warning as shown below! So once again, for the sake of security, it is best to use a Google account without personal data when you use it (so you don't have to worry about your own code hiding an agency that will steal your personal data) . If you decide to use it, click "Advanced" and then "Go to Get_Info (unsafe)". This is also only the first time it will appear, and there is no need to confirm it later. In other words, if anyone knows how to get Google to verify, please leave a message to let me know.

It's really complicated to be so suspicious of the macros I wrote

❼ After confirming, you will see the prompts to start running and finish running.

❽ Go back to the work page "Wallet Balance" to see that the information has been updated. The time is also recorded when the macro is updated, so you can know when it is updated at a glance. This column is especially important after setting up the automatic run, because at a glance you will know whether it is working properly.

The time is also recorded when the macro is updated, so you can know when it is updated at a glance

🟨 Instructions for use: Regular automatic update

After talking about so many troublesome settings, I finally got to the best place! The button to run the macro has to wait for it to finish, but if you set it to run automatically at regular intervals, you don't have to wait when you open it.

❶ Click "Extensions" >> "Apps Script"

Open Apps Script to see the program code and set the conditions for automatic running

❷ Click the “ Trigger Condition ” of the alarm clock graphic in the pop-up window.

Alarm clock graphic = trigger condition setting

❸ Click “ Add Trigger Condition ” to start setting trigger condition.

Click "Add Trigger Condition" to start setting trigger conditions

❹ Set the update 4 times a day as shown in the figure below. If you want to run more times, you can modify it in the options " Select Time Trigger Condition Type " and " Select Hour Interval ". You can run as fast as one minute.

Don't forget to click " Save " after setting.

❺ When you see that there is one more line of trigger conditions as shown in the figure below, it is completed.

🟨Additional instructions

Although it claims to be able to view all the coins on the COSMOS chain, the corresponding API must be added first. The working page " API " currently only records the following coins, other coins can go to Ping Wallet to find the corresponding API to add, if you feel troublesome, you can also support this article for 5 HKD, and leave a message for the API you want to add (up to 5 at a time, Because I'm afraid you ask me to add all of them...).

Now the template only supports these coins, because the uncle only added the API of these coins...

🟨 Articles you may have missed

Simply put, advertising for old articles?

small tools

Excel VBA

Excel Tips

🟨code

macro.gs

 function AutoRunInfo() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sht00 = ss.getSheetByName("Wallet Balance");

tR0 = sht00.getLastRow()
//SpreadsheetApp.getUi().alert('Confirmation received.');

UnitCol = 8

for (var sR0 = 2; sR0 <= tR0; sR0++) {
aToken = sht00.getRange(sR0,1).getValue().toLowerCase();
if (sht00.getRange(sR0,1).getValue() !=""){
//Pledge aURL = sht00.getRange(sR0,UnitCol+1).getValue();
try{
aValue = GetTokenAvailable(aURL,aToken)/sht00.getRange(sR0,UnitCol+0).getValue();
} catch (e) {
aValue = "Error"
}
sht00.getRange(sR0,3).setValue(aValue);

//Delegate sURL = sht00.getRange(sR0,UnitCol+3).getValue();
try{
sValue = GetTokenStake(sURL)/sht00.getRange(sR0,UnitCol+0).getValue();
} catch (e) {
sValue = "Error"
}
sht00.getRange(sR0,5).setValue(sValue);

//Reward, no delegation = no reward, so first confirm whether there is a delegation if(sValue != 0){
rURL = sht00.getRange(sR0,UnitCol+2).getValue();
try{
rValue = GetTokenReward(rURL)/sht00.getRange(sR0,UnitCol+0).getValue();
} catch (e) {
rValue = "Error"
}
} else {
rValue = 0
}
sht00.getRange(sR0,4).setValue(rValue);

totalQty = 0
if (aValue != "Error"){
totalQty = totalQty + aValue;
}

if (rValue != "Error"){
totalQty = totalQty + rValue;
}

if (sValue != "Error"){
totalQty = totalQty + sValue;
}

try{
//totalValue = GetPrice("https://api-osmosis.imperator.co/tokens/v1/"+aToken,"price");
totalValue = totalQty * GetPrice("https://api-osmosis.imperator.co/tokens/v1/"+aToken,"price");
} catch (e) {
totalValue = "Error"
}
sht00.getRange(sR0,6).setValue(totalValue);

sht00.getRange(sR0,UnitCol+4).setValue(new Date());
}
}
};

API.gs

 function GetPrice(Url,itemKey) {
var res = UrlFetchApp.fetch(Url);
var content = res.getContentText();
var jsonObject = JSON.parse(content);
return jsonObject[0][itemKey];
}

function GetTokenAvailable(Url,token) {
var res = UrlFetchApp.fetch(Url);
var content = res.getContentText();
var jsonObject = JSON.parse(content);

var xyz = Number(jsonObject["pagination"]["total"])
var amt = 0.0

if (xyz == 0 ) {
return 0;
} else if (xyz == 1) {
return Number(jsonObject["balances"][0]["amount"]);
} else {
for (var i = 0; i < xyz; i++) {
denom = String((jsonObject["balances"][i]["denom"]));
if (denom.includes(token) == true){
amt+= Number(jsonObject["balances"][i]["amount"]);
}
}
return amt;
}
}

function GetTokenStake(Url) {
try {
var res = UrlFetchApp.fetch(Url);
} catch (e) {
return 0;
}
var content = res.getContentText();
var jsonObject = JSON.parse(content);

var xyz = Number(jsonObject["pagination"]["total"])
var amt = 0.0

if (xyz == 0 ) {
return 0;
} else {
for (var i = 0; i < xyz; i++) {
amt+= Number(jsonObject["delegation_responses"][i]["delegation"]["shares"]);
}
}
return amt;
}

function GetTokenReward(Url) {
var res = UrlFetchApp.fetch(Url);
var content = res.getContentText();
var jsonObject = JSON.parse(content);
return Number(jsonObject["total"][0]['amount']);
}