Hi thank you for helping,
I am looking for a way to link 2 cells together in Google Sheets, where the output of either one will depend on the input of the other.
Let me give an example I have a database of column with codes and next to it corresponding names, simple example below.
DATABASE 1
CODE | NAME
-----------
146 | Aba
234 | Bac
365 | Cge
When filling out an order I want to be able to either put in the code and the sheet will automatically lookup the name and fill it, or put in the name and the sheet will automatically fill in the code.
example:
Order 1
my input finished
CODE | NAME CODE | NAME
----------- -> PROCESS -> -----------
146 | 146 | Aba
| Cge 365 | Cge
I am looking to figure out what the "process" could be to get the the desired solution. I am not good at coding myself and don't really know where I could begin, but I was thinking that one solution could be using the VLOOKUP function to go down each row, check if there is a value in the CODE column, then VLOOOKUP the corresponding NAME value from database, if no CODE value, then see if there is a NAME value and VLOOKUP the CODE value. Then move to the next row.
Thank you for taking your time to read this I appreciate any help.
You can perform what you describe with these functions. I have ascii to character and column letter to column numbers examples. Copy all of these functions into Code.gs and then you have to go to a blank page and rename it to 'Contacts' and then run the setupOnEdit() function. I chose to use columns A and B. The way it's setup now if you type spreadsheet columns in letters then column numbers will appear in the adjacent column. Or if you type in columns numbers then the letters will appear in the other column. I think you just wanted each column to go one way. And that's possible to. I will leave that as an exercise for the reader.
function getASCIIObject(){
var aObj={};
for(var i=32;i<127;i++){
var s=Utilities.formatString('%s',String.fromCharCode(i));
if(s!='='){
aObj[i]=s;
aObj[s]=i;
}
}
return aObj;
}
function getColumnsObject(){
var cObj={};
var chA=[];
var colA=[];
for(var i=65;i<=90;i++){chA.push(String.fromCharCode(i));}
for(var i=0;i<10;i++){
for(var j=0;j<26;j++){
if(i==0){
colA.push(chA[j]);
}
if(i>0){
colA.push(chA[i-1] + chA[j]);
}
}
}
for(var i=0;i<colA.length;i++){
cObj[i+1]=colA[i];
cObj[colA[i]]=i+1;
}
return cObj;
}
function dCode(key,mode){
if(key){
var mode=mode || 'ASCII';
if(mode=='ASCII'){
var dObj=getASCIIObject();
return dObj[key];
}
if(mode=='COLUMNS'){
var dObj=getColumnsObject();
return dObj[isNaN(key)?key.toUpperCase():key];
}
if(mode=='DICT'){
var dObj=getMyDictionary();
return dObj[key];
}
}
throw('Error; Invalid params in dCode()');
}
function setupEditTrigger(funcName) {
if(ScriptApp.getProjectTriggers().indexOf(funcName)==-1){
ScriptApp.newTrigger(funcName).forSpreadsheet(getGlobal('SSId')).onEdit().create();
}
}
function setupOnEdit(){
setupEditTrigger('decodeColumns');
}
function decodeColumns(e){
var rg=e.range;
var sh=rg.getSheet();
var col=rg.getColumn();
var row=rg.getRow();
var value=rg.getValue();
Logger.log('rg: %s sh: %s col: %s row: %s value: %s',rg.getA1Notation(),sh.getName(),col,row,value);
if(sh.getName()=='Contacts' && col==1){
rg.offset(0,+1).setValue(dCode(value,'DICT'));
}
if(sh.getName()=='Contacts' && col==2){
rg.offset(0,-1).setValue(dCode(value,'DICT'));
}
}
function getMyDictionary(sheetname,keycolumn){
var sheetname=sheetname ||'Dictionary';
var keycolumn=keycolumn || 1;
var valuecolumn=keycolumn + 1;
var dObj={};
if(sheetname && keycolumn){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName(sheetname);
var rg=sh.getRange(1,keycolumn,sh.getLastRow(),2);
var codeA=rg.getValues();
for(var i=0;i<codeA.length;i++){
if(codeA[i][0]){
dObj[codeA[i][0]]=codeA[i][1];
}else{
break;
}
}
return dObj;
}else{
throw('Error: Invalid params in getMyDictionary()');
}
}
Here's a second version that gives you two dictionary tabs to work with. One is Dictionary and the other is Dictionary2. But of course you can change them to whatever you wish.
function getASCIIObject(){
var aObj={};
for(var i=32;i<127;i++){
var s=Utilities.formatString('%s',String.fromCharCode(i));
if(s!='='){
aObj[i]=s;
aObj[s]=i;
}
}
return aObj;
}
function getColumnsObject(){
var cObj={};
var chA=[];
var colA=[];
for(var i=65;i<=90;i++){chA.push(String.fromCharCode(i));}
for(var i=0;i<10;i++){
for(var j=0;j<26;j++){
if(i==0){
colA.push(chA[j]);
}
if(i>0){
colA.push(chA[i-1] + chA[j]);
}
}
}
for(var i=0;i<colA.length;i++){
cObj[i+1]=colA[i];
cObj[colA[i]]=i+1;
}
return cObj;
}
function dCode(key,mode){
if(key){
var mode=mode || 'ASCII';
if(mode=='ASCII'){
var dObj=getASCIIObject();
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
if(mode=='COLUMNS'){
var dObj=getColumnsObject();
key=isNaN(key)?key.toUpperCase():key;
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
if(mode=='DICT1'){
var dObj=getMyDictionary();
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
if(mode=='DICT2'){
var dObj=getMyDictionary('Dictionary2');
if(dObj.hasOwnProperty(key)){
return dObj[key];
}
}
}else{
throw('Error; Invalid params in dCode()');
}
return nill;
}
function setupEditTrigger(funcName) {
if(ScriptApp.getProjectTriggers().indexOf(funcName)==-1){
ScriptApp.newTrigger(funcName).forSpreadsheet(getGlobal('SSId')).onEdit().create();
}
}
function setupOnEdit(){
setupEditTrigger('decodeColumns');
}
function decodeColumns(e){
var rg=e.range;
var sh=rg.getSheet();
var col=rg.getColumn();
var row=rg.getRow();
var value=rg.getValue();
Logger.log('rg: %s sh: %s col: %s row: %s value: %s',rg.getA1Notation(),sh.getName(),col,row,value);
if(sh.getName()=='Contacts' && col==1){
rg.offset(0,+1).setValue(dCode(value,'DICT1'));
}
if(sh.getName()=='Contacts' && col==2){
rg.offset(0,-1).setValue(dCode(value,'DICT2'));
}
}
function getMyDictionary(sheetname,keycolumn){
var sheetname=sheetname ||'Dictionary';
var keycolumn=keycolumn || 1;
var valuecolumn=keycolumn + 1;
var dObj={};
if(sheetname && keycolumn){
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName(sheetname);
var rg=sh.getRange(1,keycolumn,sh.getLastRow(),2);
var codeA=rg.getValues();
for(var i=0;i<codeA.length;i++){
if(codeA[i][0]){
dObj[codeA[i][0]]=codeA[i][1];
}else{
break;
}
}
return dObj;
}else{
throw('Error: Invalid params in getMyDictionary()');
}
}