Reputation: 1317
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?
Upvotes: 0
Views: 789
Reputation: 64100
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