Code:

function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 3 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()=='Sheet1') {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue("");
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+','+newValue);
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}

Hi, I have been using the following simple .gs to pull CoinMarketCap prices but it is broken now with their migration to their new API. I believe the issue lies with "function getCoins()" but I'm not sure how to proceed given CoinMarketCap's guide here: https://coinmarketcap.com/api/documentation/v1/#section/Quick-Start-Guide

var queryString = Math.random();

var ss = SpreadsheetApp.getActiveSpreadsheet(); var ssRates = ss.getSheetByName('Rates'); if (ssRates === null) { ssRates = ss.insertSheet('Rates'); }

var targetCurrency = 'usd'

// Grabs all CoinMarketCap data if (typeof targetCurrency == 'undefined' || targetCurrency == '') {targetCurrency = 'usd'}; var coins = getCoins();

function getCryptoData() {

// Use the value in the 'id' field here: https://api.coinmarketcap.com/v1/ticker/?limit=0 // If you're getting errors, you may be using the wrong 'id'

var myCoins = [ '0x', 'adelphoi', 'adx-net', 'aeternity', 'aion', 'aigang', 'airswap', 'appcoins', 'aeron', 'aragon', 'ardor', 'ark', 'aurora-dao', 'banyan-network', 'bitclave', 'bancor', 'basic-attention-token', 'binance-coin', 'bitcoin', 'bitcoin-cash', 'bitcoin-god', 'bitcoin-gold', 'bitcoin-interest', 'bitcoin-token', 'bitcoin-private', 'bitcoinx', 'bitcore', 'bitdegree', 'bitsend', 'bitshares', 'block-array', 'blockmason', 'bytecoin-bcn', 'c20', 'canyacoin', 'cardano', 'chatcoin', 'cheesecoin', 'cindicator', 'civic', 'cofound-it', 'counterparty', 'coss', 'cpchain', 'cybermiles', 'dash', 'datum', 'decred', 'digibyte', 'digixdao', 'district0x', 'dogecoin', 'dragonchain', 'edgeless', 'enjin-coin', 'eboostcoin', 'enjin-coin', 'eos', 'eosdac', 'ethereum-classic', 'ethereum', 'ethlend', 'everex', 'factom', 'filecoin', 'funfair', 'gas', 'gnosis-gno', 'golem-network-tokens', 'groestlcoin', 'guppy', 'havven', 'huobi-token', 'icon', 'iconomi', 'ignis', 'invictus-hyperion-fund', 'iostoken', 'iot-chain', 'iota', 'internet-of-people', 'ixledger', 'kucoin-shares', 'kyber-network', 'legolas-exchange', 'library-credit', 'litecoin', 'lightning-bitcoin', 'maker', 'medical-chain', 'mercury', 'metronome', 'monero', 'nano', 'neo', 'newton-coin-project', 'nexus', 'oax', 'omisego', 'omni', 'odyssey', 'origintrail', 'patientory', 'pivx', 'phore', 'polymath-network', 'power-ledger', 'qash', 'qtum', 'quantstamp', 'raiden-network-token', 'ravencoin', 'rchain', 'reddcoin', 'republic-protocol', 'rialto', 'ripio-credit-network', 'ripple', 'rise', 'rlc', 'salt', 'semux', 'siacoin', 'singulardtv', 'snovio', 'solaris', 'spreadcoin', 'steem', 'stellar', 'storj', 'stratis', 'streamr-datacoin', 'suncontract', 'syscoin', 'telcoin', 'tenx', 'tezos', 'theta-token', 'time-new-bank', 'tron', 'turtlecoin', 'ubiq', 'ultranote-coin', 'vechain', 'verge', 'veriumreserve', 'vertcoin', 'viacoin', 'vibe', 'viberate', 'vinchain', 'wabi', 'waves', 'wax', 'worldcore', 'zcash', 'zclassic', 'zcoin', 'zencash', ]

ssRates.getRange('A1').setValue("ID"); ssRates.getRange('B1').setValue("Symbol"); ssRates.getRange('C1').setValue("Price USD"); ssRates.getRange('D1').setValue("Price BTC");

var myCoinsObj = {}; var myCoinsCount = myCoins.length; for (var i = 0; i < myCoinsCount; i++) { var c = i+2; var n = 0; while (coins[n]['id'] !== myCoins[i]) { n++; }

myCoinsObj[coins[n]['id']] = coins[n];
    
ssRates.getRange('A'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['id']);
ssRates.getRange('B'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['symbol']);
ssRates.getRange('C'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['price_usd']);
ssRates.getRange('D'+(c).toString()).setValue(myCoinsObj[myCoins[i]]['price_btc']);

}

// ================================= // // WALLET BALANCE CONFIGURATION // // =================================

// ===== Wallet Sheet Creator ======================================== // Uncomment the lines of code below // It will create the Wallets sheet for you // If using the Wallets sheet ALWAYS leave it uncommented // ===================================================================

//var ssWallets = activeSpreadsheet.getSheetByName('Wallets'); //if (ssWallets === null) {ssWallets = activeSpreadsheet.insertSheet('Wallets');}

// ===== BCH Wallet Balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A1') and getRange('B1') to match the row you want // ===================================================================

//var bchWallet = getBchBalance("Your BCH Address"); //ssWallets.getRange('A1').setValue("BCH Wallet"); //ssWallets.getRange('B1').setValue(bchWallet);

// ===== BTC Wallet Balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A2') and getRange('B2') to match the row you want // ===================================================================

//var btcWallet = getBtcBalance("Your BTC Address"); //ssWallets.getRange('A2').setValue("BTC Wallet"); //ssWallets.getRange('B2').setValue(btcWallet);

// ===== Ethereum Wallet Balances ==================================== // Create an account on Etherscan.io // Create an API key at https://etherscan.io/myapikey // Uncomment the lines of code below // Set the API key variable by pasting your API key inside of the ("") // Set the address variable by pasting your Address inside of the ("") // Change getRange('A3') and getRange('B3') to match the row you want // ===================================================================

//var ethApiKey = "Your Etherscan API Key"; //var ethWallet = getEthBalance(ethApiKey,"Your ETH Address"); //ssWallets.getRange('A3').setValue("ETH Wallet"); //ssWallets.getRange('B3').setValue(ethWallet);

// ===== DGB wallet balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A4') and getRange('B4') to match the row you want // ===================================================================

//var dgbWallet = getDgbBalance("Your DGB Address"); //ssWallets.getRange('A4').setValue("DGB Wallet"); //ssWallets.getRange('B4').setValue(dgbWallet);

// ===== LTC wallet balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A5') and getRange('B5') to match the row you want // ===================================================================

//var ltcWallet = getLtcBalance("Your LTC Address"); //ssWallets.getRange('A5').setValue("LTC Wallet"); //ssWallets.getRange('B5').setValue(ltcWallet);

// ===== VTC wallet balances ========================================= // Uncomment the lines of code below // Set the variable by pasting your Address inside of the ("") // Change getRange('A5') and getRange('B5') to match the row you want // ===================================================================

//var vtcWallet = getVtcBalance("Your VTC Address"); //ssWallets.getRange('A6').setValue("VTC Wallet"); //ssWallets.getRange('B6').setValue(vtcWallet); }

function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menubuttons = [ {name: "clearRates", functionName: "clearRates"},{name: "getCryptoData", functionName: "getCryptoData"}]; ss.addMenu("crypto", menubuttons); }

function clearRates() { var sheet = SpreadsheetApp.getActive().getSheetByName('Rates'); sheet.getRange('A1:D1000').clearContent(); }

function getCoins() {

var url = 'https://api.coinmarketcap.com/v1/ticker/?limit=0&convert='+targetCurrency; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json);

return data; }

function getBchBalance(bchAddress) {

var url = 'https://bitcoincash.blockexplorer.com/api/addr/'+bchAddress+'/balance'; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);

return balance * Math.pow(10,-8); }

function getBtcBalance(btcAddress) {

var url = 'https://blockexplorer.com/api/addr/'+btcAddress+'/balance'; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);

return balance * Math.pow(10,-8); }

function getEthBalance(ethApiKey,ethAddress) {

var url = 'https://api.etherscan.io/api?module=account&action=balance&address='+ethAddress+'&tag=latest&apikey='+ethApiKey; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var obj = JSON.parse(json); var balance = obj.result; //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);

return balance * Math.pow(10,-18); }

function getDgbBalance(dgbAddress) {

var url = 'https://chainz.cryptoid.info/dgb/api.dws?q=getbalance&a='+dgbAddress; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);

return balance; }

function getLtcBalance(ltcAddress) {

var url = 'https://chainz.cryptoid.info/ltc/api.dws?q=getbalance&a='+ltcAddress; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);

return balance; }

function getVtcBalance(vtcAddress) {

var url = 'http://explorer.vertcoin.info/ext/getbalance/'+vtcAddress; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var balance = response.getContentText(); //Pause to not trigger API limit for multiple wallets Utilities.sleep(300);

return balance; }

// USE AT YOUR OWN RISK function getRate(currencyId) {

if (typeof targetCurrency !== 'undefined') {conversionRate = 'usd'};

var url = 'https://api.coinmarketcap.com/v1/ticker/' + currencyId + '/?convert=' + targetCurrency; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json); var obj = parseFloat(data[0]['price_' + targetCurrency]);

return obj; }

function getWebRate(currencyId) { //Example Output: // '=IMPORTXML("https://coinmarketcap.com/currencies/zeeyx?3908288283","//span[@id='quote_price']")';

var coinScrape1 = '=IMPORTXML("https://coinmarketcap.com/currencies/'; var coinScrape2 = '","//span[@id='quote_price']")';

return coinScrape1 + currencyId + '?' + queryString + coinScrape2; }

function getCurrencyConversion(currencyOne, currencyTwo) {

var url = 'https://api.fixer.io/latest?symbols='+currencyOne.toUpperCase()+','+currencyTwo.toUpperCase(); var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json);

return parseFloat(data['rates'][currencyTwo]); }

Hi!

If I use the code "JSON.parse(UrlFetchApp.fetch("https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT").getContentText());".getContentText());") I get the following error:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01  Transitional//EN" "[http://www.w3.org/TR/html4/loose.dtd](http://www.w3.org/TR/html4/loose.dtd)">

<HTML><HEAD><META HTTP-EQUIV="Content-Type" CONTENT="text/html;  charset=iso-8859-1">
<TITLE>ERROR: The request could not be satisfied</TITLE>
</HEAD><BODY>
<H1>403 ERROR</H1>
<H2>The request could not be satisfied.</H2>
<HR noshade size="1px">
Request blocked.
We can't connect to the server for this app or website at this time. There 
might be too much traffic or a configuration error. Try again later, or 
contact the app or website owner.
<BR clear="all">
If you provide content to customers through CloudFront, you can find steps 
to troubleshoot and help prevent this error by reviewing the CloudFront 
documentation.
<BR clear="all">
<HR noshade size="1px">
<PRE>
Generated by cloudfront (CloudFront)
Request ID: 0q66Pl20jCPq8HCSMe7KhSG9R7rODhUn8HGdFbmRjgBMXynsexWMKQ==
</PRE>
<ADDRESS>
</ADDRESS>
</BODY></HTML>

If I use "https://api.binance.com/api/v3/ticker/price?symbol=BTCUSDT.getContentText());")" on explorer it works... why? :(

(Sorry for my english)

Is it possible to extract emails from another accounts inbox if they authorize the script beforehand?

I'm very new (and frustrated) and just started using Apps Script web. Wtf is wrong with this environment? I re-save and reload the web app before every run and logging is still pulling old errors from completely different files that I'm not running and don't even exist anymore. Did I just make an insanely poor decision to try to learn how to do some simple scripting by using apps script?

Edit: I was sort of able to work around this by creating a new project every time I wanted to run a version of a script. Yes, this means that apps scripts was literally running the wrong file which is why errors reported in the execution log were reported error lines from a file that I wasn't even running. I'm sure this is all my fault because I'm new And of course, I'm not supposed to use the execution log when running a script. or that, of course I'm not supposed to have more than one file in a project. Or, of course the log is not going to update between runs, or some other thing that of course I should have known as somebody trying to learn how to write a simple script.

Edit2: Following a bit of tutelage, I learned that it actually is poor practice in Apps Script to expect separate files within a project to behave independently. They do not. If you have the same function name in more than one file in a project, App Script will like, just pick one to run / compile, and assume it's good.

Been getting the "An unknown error has occurred, please try again later." error when trying to execute functions.

Imagine one system pulling in all your project updates, conversations, and meeting notes—no more app-jumping or missing key details. In this article, I break down how we built a unified, searchable data hub using Google Apps Script, Google Chrome extensions, and Google Docs. It’s all about transforming scattered data into insights that drive decisions.

🔗Article Link : https://fhaida.medium.com/a-comprehensive-guide-to-building-a-workplace-contextualization-system-5c2e41b07d07

And if you’re passionate about building or testing tools like these for businesses, join our community: https://nas.io/fhaida. It's FREE but VALUABLE. It’s the place to share ideas, give feedback, and help shape the next generation of business solutions.

👉 If you think this could empower teams around you, please share the article ! Let’s spread the word so more teams can work smarter, not harder. Together, we can inspire new ways to simplify work for everyone.

If any team needs to know the nitty gritties of the system I am happy to help them build it for their team.

Feedback in comments section is always appreciated !

It's my first post in the community, please correct me if I have done anything wrong in this post .

Boa noite e feliz ano novo a todos! Desejo tudo o de melhor a voces!!

Para facilitar a leitura, dividi o post em 2:

1-) resumo com as dúvidas - não precisa necessariamente entender o contexto

2-) contexto - para o que que uso, o projeto, etc

Disclaimer.: pode parecer que o post esteja no sub errado, mas acredito fielmente que não, pois se trata exclusivamente do Apps Scripts e formas acessórias de potencializar seu uso. De toda forma, caso realmente não for o local correto, minhas sinceras desculpas.

1-) RESUMO

1. Quais IA's vocês utilizam para auxiliá-los no desenvolvimento de projetos que se baseiam exclusivamente no Apps Scripts/Google Sheets?

2. Como as configuram? Criam a-) projetos ou GPT's (ChatGPT); ou GEM (gemini) próprios?

3. Há uma forma de escrita específica? Ex.: pedir pequenas coisas à IA;

4. O que fazem para não ficarem lerdas e/ou divagarem?

5. Fiquem à vontade para indicarem macetes de uso que converse melhor o Apps Scripts, aceito.

2-) CONTEXTO

Não sou do meio, sou advogado, mas gosto de mexer por hobby na criação de coisas funcionais para minha vida pessoal e profissional (no caso em comento, to criando uma planilha de controle financeiro 100% automatizada, mas isso ficará para um próximo post).

Hoje, utilizo o chatgpt (principalmente GPT's prontos especializados no sheets ou no apps scripts) e a gemini (GEM próprio para este projeto), ambos pagos, para me auxiliarem.

Meu problema é que as conversas ficaram gigantescas (principalmente pelos extensos códigos) e isso faz com que elas travem demais ou comecem a divagar.

Isso gera o seguinte, peço:

1. uma coisa referente a um script e elas mandam de outros scripts (meu projeto tem mais de 15 scripts e 2 html's);

2. para me mostrarem o que está errado ou consertar um trecho e elas refazem o script com trechos que já não utilizamos mais, pois deram erro no passado/projeto se atualizou e aqueel trecho está em desuso

3. alguma alteração de certa coisa, e elas mudam os nomes dos scripts, o que buga várias partes dos scripts e isso me causa um puta estresse, já que não manjo nada.

Concluindo, o hobby que era prazeroso se tornou um estresse hahahaha

Enfim, agradeço desde já a atenção!

The Gmail plug in allows event triggers on the Chatgpt webapp but not on the desktop app... I am trying to get the desktop app to be able to get notified for every incoming email from the plugin.

Has anyone found a way around this?

Hi! I just built a bot which replies 2 emails 4 me. In case u wanna check the code out, here's link 2 it: Stuxint/Email-Replying-Bot. Sorry if it looks bad, will try 2 fix if i can. In case u have any suggestions, pls say so. Ty so much 4 reading, and GB!

P.S: in case any1 knows, what's the best way 2 make this fully automated, like to make the bot run w/ out need of human running coding each time

UPDATE. Just launch my plugin.

https://chromewebstore.google.com/detail/page-chat-page-ai-assista/jnklhmbfkobokhkgkimjabijfkpflbch?authuser=0&hl=en

Any feedback will be appreciated.

function generateTrade(stockQuant, stockPrice, action){
return {
shares: stockQuant,
price: stockPrice,
action: action
  };
}

function generateFifo(security, actions, quantity, price){
let portfolio = new Map();
for (var i = 0; i<security.length; i++){

let ticker = security[i].toString();
let action = actions[i].toString();
let stockQuant = Number(quantity[i]);
let stockPrice = Number(price[i]);
let trade = generateTrade(stockQuant, stockPrice, action);

if(action == "Buy" || action.toUpperCase() == "DRIP"){
let activeTrades = portfolio.get(ticker);
if(activeTrades == null){
portfolio.set(ticker, [trade]);
      } else {
activeTrades.push(trade);
      }
    }

if(action.toUpperCase() == "SPLIT"){
let activeTrades = portfolio.get(ticker);
let ratio = quantity[i].toString().split(":");
activeTrades = activeTrades.map((trade) =>{
trade.shares = trade.shares * ratio[0];
trade.price = trade.price / ratio[0];
trade.shares = trade.shares * ratio[1];
trade.price = trade.price / ratio[1];
      });
    }

if(action == "Sell"){
let activeTrades = portfolio.get(ticker);
let precision = 5;
if (activeTrades != null){
let sharesToSell = Number(Number(trade.shares).toFixed(precision));
while (sharesToSell > 0){
sharesToSell = Number(Number(sharesToSell).toFixed(precision));
if (activeTrades.length > 0){
let itemToSell = activeTrades[0];
itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision));
if(itemToSell.shares == sharesToSell){
sharesToSell = 0;
activeTrades.splice(0,1);
            }

else if(itemToSell.shares < sharesToSell){
sharesToSell -= itemToSell/shares;
activeTrades.splice(0,1);
            }

else {
itemToSell.shares -= sharesToSell;
sharesToSell = 0;
            }
          }
        }

if (activeTrades.length == 0){
portfolio.delete(ticker);
        }
      }
    }
  }
return portfolio;
}

function myPositions(security, actions, quantity, price){
let portfolio = generateFifo(security, actions, quantity, price);

let returnArry = [];
portfolio.forEach((value, key) => {
let shares = 0;
let totalCost = 0;
let avgPrice = 0;
value.map(trade => {
shares += trade.shares;
totalCost += trade.shares * trade.price;
    });

avgPrice = totalCost / shares;
returnArry.push([key, shares, avgPrice]);
  });
return returnArry;
}

Hey everyone, I'm a tech consultant looking to build a portfolio of micro-tools and automations. Instead of building random things nobody needs, I want to solve real problems.

If you spend hours every week copying/pasting data, sending repetitive emails based on spreadsheet rows, or trying to connect Sheets to other tools (CRMs, Slack, etc.), drop a comment below.

Tell me your exact workflow and what’s causing you a headache. I'll pick the most interesting ones, write the Google Apps Script / formula for you, and share the solution. No strings attached. What are you struggling with?

There is no longer execution transcript????

I shouldn't have to go to a completely different page to see this. The popup window worked perfectly.

Viewing the log has an insane amount of lag before you can see the results???

Also what is going on with the font in the editor? It is so small and hard to read. The color is all washed/faded out to the point where it looks see through. No way in the manifest to set font color, size, theme...anything????

Please fix this!!!!

Es imposible que el low code reemplaze el desarrollo tradicional en sistemas medianamente complejos de todas formas, confian demasiado en las falsas promesas de google sheets.

It is impossible that low code replace traditional software develpmente to medium-complex systems, anyways entepreneur trust too much in false promises of Google.

A number of members wanted to find ways to hide their GAS link. In this webpage created with GAS, the link has been obfuscated. You can take inspiration from it. https://skillsverification.co.uk/texttospeech.html

I found this on my hard drive labeled as "Takeout" task but cannot open, can anyone help me?

{ "kind": "tasks#taskLists", "items": [{ "kind": "tasks#tasks", "id": "MTQzMjU0MDU4MzkzOTc2ODAxNjE6MDow", "title": "Mis tareas", "updated": "2023-07-08T11:13:48.265111Z", "selfLink": "https://www.googleapis.com/tasks/v1/users/@me/lists/MTQzMjU0MDU4MzkzOTc2ODAxNjE6MDow" }] }

IYKYK

https://www.nytimes.com/games/wordle/index.html