Google-sheets – Change currency formatting of cell with dropdown list

google sheetsgoogle-apps-script

Next week I am going to travel to Europe and I wanted to have in a spreadsheet all my spendings, but as I will need to use both Euros and British Pounds, I need to have a way to set the currency used that time. I thought of using a dropdown list to select the currency, but I can't find a way to change the currency formatting of the amount cell based on the data entered in the list.

Can someone please help me make a script that can do this for me?

Best Answer

As requested this is one way to do it in google app scripts:

function onEdit(e){
  var sheetName = "Currency"; //name of sheet we want to adjust formatting
  var currencyCol = 1; //column A
  var amountCol = 2; //column B
  var defaultFormat = "[$$]#,##0.00";
  var currencyFormat = {"USD":"[$$]#,##0.00",
                        "POUND":"[$£]#,##0.00",
                        "EURO":"[$€]#,##0.00"};
  var r = e.range;
  if(e.source.getSheetName()==sheetName && r.getColumn() == currencyCol){ //we are assuming you will edit one cell at a time to simplify this
    var uf = currencyFormat[r.getValue()];
    uf = uf?uf:defaultFormat;
    r.offset(0,amountCol-currencyCol).setNumberFormat(uf);
  }
}

Personally I would rather just add a third helper column where I would put =if(A3="EURO",text(B3,"[$£]#,##0.00")) etc and have the column I type things in just be formatted as a regular number. Either way you are going to have to type or select the currency type, although if you had the keyboard to do it you could even just type in the currency symbol.

Related Solutions

Google Sheets – Sorting Data by Cell Entry

Here's an Apps Script that will build a second sheet based on the first (javascript-only approach).

I only tested it on a simple 3x5 version of your first set of data, with row 1 (array index 0) being the headers.

It fails if the second sheet already exists - it's not pretty. But it, and the Apps-script tutorials, should get you started.

function buildSheet2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var students = {};  // for a map of students indexed by name, mapping to a string of events separated by "|"
  for(var i=1; i<data.length; i++){
    var eventName = data[i][0];
    Logger.log(eventName);

    for (var j=1; j<3; j++){
      var studentName = data[i][j];
      if (studentName != "") {
        if (!(studentName in students)) {
          students[studentName] = eventName;
        } else {
          students[studentName] = students[studentName] + '|' + eventName;
        }
      }
    }
  }
  Logger.log(students);
  // insert a new sheet and make it active  
  var sNameSheet = SpreadsheetApp.getActive().insertSheet("By student name");
  SpreadsheetApp.setActiveSheet(sNameSheet);
  var newData = [["Name", "Event", "Event", "Event"]];
  var studentKeys = Object.keys(students);
  // split the events for each student into columns
  for (s in studentKeys) {
    var newRow = ["Name", "Event", "Event", "Event"];
    Logger.log (studentKeys[s]);
    newRow[0] = studentKeys[s];
    // clear out temp row
    for (i=1; i<newRow.length; i++) {
      newRow[i] = "";
    }
    var events = students[studentKeys[s]].split("|");
    Logger.log(events);
    for (i=0; i<events.length; i++) {
      newRow[i+1] = events[i];
    }
    newData.push(newRow);
    Logger.log(newRow);
  }
  Logger.log(newData);
  // write data to new sheet
  sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Google Sheets – How to Filter Based on Dropdown List of Months

To convert YYYY, MM, DD to an actual Date object, you need the DATE command. The following works:

=filter(A6:F371,A6:A371>=DATE(2015,1,1),A6:A371<=DATE(2015,1,31))

But I see you want to filter based on dropdown entry. For this I would use MONTH function. For example, suppose cell J10 has validation rule: list of items

01 (January), 02 (February), 03 (March), and so on

Then your filter in J11 could be

=filter(A6:F371,MONTH(A6:A371)=VALUE(LEFT(J10,2)))

The idea here is that the names of month help the user in selection, but the filter uses the numeric format (month number).

Best Answer

Related Solutions

Google Sheets – Sorting Data by Cell Entry

Google Sheets – How to Filter Based on Dropdown List of Months

Related Topic