CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
sojim2
sojim2

Reputation: 1317

Set value based on drop down selection using script

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: enter image description here

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

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?

Upvotes: 0

Views: 790

Answers (1)

Cooper
Cooper

Reputation: 64110

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;
}

Upvotes: 1

Related Questions