google sheetsgoogle-finance
I would like to add an automatic conversion thing to my spreadsheet.
I have two columns, A1 titled Money in USD and B1 titled Money in BRL.
I want to make it so whenever I enter a value in the USD column, it automatically enters the BRL value to the BRL column.
I found this:
=GoogleFinance("CURRENCY:"USDBRL")
…but I don't really know how to work with it for my purpose. And basically, I don't know how to work with any functions at all.
Is that easy enough or even possible?
Maybe this has been fixed in New Google Sheets because it works for me (indeed, it is VERY simple):
Select A1:B3, Insert, Chart..., click Line chart, Insert.
Seems happy to work with DMY format and, as it happens, text for dates (since happening to be 4 months apart the x-axis spacing to be equal is about correct anyway).
This is a much shorter script that does it:
function onEdit(e) {
if ([2, 5, 8, 11].indexOf(e.range.columnStart) != -1) {
e.range.offset(0, 1).setValue(new Date());
}
}
Where [2, 5, 8, 11] is the list of columns of interest. The offset(0, 1) means same row, 1 column to the right.
If you want a particular format of the timestamp, add setNumberFormat like so: (reference for formats)
e.range.offset(0, 1).setValue(new Date()).setNumberFormat("yyyy.MM.dd at HH:mm");
Various versions of the script you found are now entrenched in forums despite many things being wrong with it. It does not preserve date structure, replacing it with a string. It is tied to a timezone. It does not care about Daylight Saving Time. It uses too many API calls.
Best Answer
The value of
GoogleFinance("CURRENCY:USDBRL")is the value of one US dollar in Brazilian currency. This needs to be multiplied by the dollar amount that you want to convert. So, if B5 is the USD amount, the formulawill give the equivalent amount in Brazilian currency.