copy a value from one cell to another cell per row as "value" in Google sheet

1
votes

I'm working in a Google sheet where I have several custom functions that geocode an address into lat/long details. But I want to copy this value into another column on the same sheet as a value so the end result remain static. I found a working piece of code that does this for an entire range but I can't get it dynamic so I can let it work for a single row and use the function per row where I need it.

Below is the code I have already. So when I go to cell N2 and call =getRangeValues(), it will copy the values for all fields from M2 until M9 into N2:N9 but that's not what I want. I want it to only copy it for M2 into N2 for a single row. I tried replacing the getRange into ActiveCell but that doesn't to the trick.

Anybody who can help me out with this?

function getRangeValues() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("M2:M9");
  var values = range.getValues();
  return values;
};
1
google-apps-scriptgoogle-sheets
What do mean by "can't get it dynamic"? Does "I want it to only copy it for M2 into N2 for a single row" mean you want this function to always reference cells M2 and N2 or are you asking for a general solution to copy a value from one column to the neighboring column to the right for any given row? – dwmorrin

1 Answers

2
votes

Maybe you want something like that:

function getRangeValues() {
  
  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  const cc = sheet.getCurrentCell();
  return sheet.getRange(cc.getRow(),cc.getColumn()-1).getValue();
};

If you click on a particular cell and enter the formula: =getRangeValues() it will give you the value of the left cell in the same row:

example