Set value based on drop down selection using script

Question

Because prices change over time, I don't want to tie in the price permanently to another cell. So I'd like the script to set the price based on drop down selection.

I have Services sheet with Cost column:

I'd like the script to apply the cost based on selection like so (in Selection sheet):

Here's the example spreadsheet: https://docs.google.com/spreadsheets/d/1O1rZUstDNSXPdUVXvaDfPO4rAQs2cJWHimfGxbddtNU/edit#gid=232108540

How can I set value Cost in Services sheet to Selection sheet with script?

Why not just use a VLOOKUP, as a lookup of the selected service in the Selection sheet to the cost sheet, like so - =IFERROR(VLOOKUP(A2,Services!A:B,2,FALSE),0)? — Joshua T
– Joshua T, Commented May 28, 2019 at 16:29
I don't want to permanently tie the cost value to a cell since the cost can change over time, and I want to keep historical pricing — sojim2
– sojim2, Commented May 28, 2019 at 21:19

Cooper · Accepted Answer · 2019-05-28 22:23:56Z

1

Try this:

function cost() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Services');
  var rg=sh.getRange(2,1,sh.getLastRow()-1,2);
  var vA=rg.getValues();
  var costObj={}
  for(var i=0;i<vA.length;i++) {
    costObj[vA[i][0]]=vA[i][1];
  }
  var selsh=ss.getSheetByName('Selection');
  var selrg=selsh.getRange(2,1,selsh.getLastRow()-1,2);
  var vB=selrg.getValues();
  for(var j=0;j<vB.length;j++) {
    vB[j][1]=costObj[vB[j][0]];
  }
  selrg.setValues(vB);
  //if you only want to set columnB you can do this instead of the above line
  //var vC=vB.map(function(r){return [r[1]]});
  //selsh.getRange(2,2,vC.length,1).setValues(vC);
}

As an onEdit():

function cost(e) {//Installable onEdit()
  var sh=e.range.getSheet();
  var name=sh.getName();
  if(name!='Selection')return;
  if(e.value && e.range.columnStart==1) {
    e.range.offset(0,1).setValue(getCostObj()[e.value]);
  }
}  
function getCostObj() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('Services');
  var rg=sh.getRange(2,1,sh.getLastRow()-1,2);
  var vA=rg.getValues();
  var costObj={}
  for(var i=0;i<vA.length;i++) {
    costObj[vA[i][0]]=vA[i][1];
  }
  return costObj;
}

edited May 28, 2019 at 22:23

answered May 28, 2019 at 18:54

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

sojim2 Over a year ago

This only adds cost value once we execute the script, we can simply change it to onEdit?

Cooper Over a year ago

It probably would be better to do it as a cell function as @Joshua T suggested. I think that we tend to use onEdit way too much.

sojim2 Over a year ago

but that wouldn't keep historical pricing if prices change, and I'd like to keep historical pricing? can we make the script so that it's efficient with onEdit, it looks like this script scans entire column and adds script, which may be inefficient for the onEdit needs

Cooper Over a year ago

Okay. What user action initiates the onEdit() event? What Cell? What Column? ...

Cooper Over a year ago

I had this line incorrect e.range.offset(0,1).setValue(getCostObj[e.value]); and I corrected it to this e.range.offset(0,1).setValue(getCostObj()[e.value]);

|

Collectives™ on Stack Overflow

Set value based on drop down selection using script

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related