is there any formula or script to copy only values (not formulas) from one column to another?. I don’t want to use CTRL + SHIFT + V because I need this to be automatic. For example:
Column A Column B
Value1
Value2
Value3
All the values of column A are calculated with an array formula, I need that everytime that column A has a new record the value passes to column B for ever, so if the value or formula in column A is deleted the copied value remains in column B, is it possible to do this?
I’m asking this because My App uses some custom formula to get GPS coordinates, and the formula executes everytime my sheet has a new record, So I want to have a backup column of this coordinates
Hi Alfred, just some of my thoughts regarding this.
About the situation, I interpret it as below:
You have column A with automated update from ARRAYFORMULA (can’t be changed).
You need a column B with only values from column A. It will be updated whenever column A has a new value, but won’t be updated when a value is deleted from column A.
Rohan’s IMPORTRANGE proposal won’t work in my opinion, because it also automatically updates the values from column A into your new range, hence when a value is deleted from column A, that value will also be deleted from column B.
What you need should be a script to:
Check when column A has new values (excluding cases when that ‘new’ value is null), this should be done row-by-row.
Update the new value to column B when the ‘new’ value is not null, otherwise keep the old value.
Hello @ThinhDinh I have made some small exercices and it is correct what you say, if a value in column A is deleted it also is deleted on column B. That script you are talking about seems interesting, I was looking for something like that but I can’t find any.
function copy_rows() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var lastRow = sheet.getLastRow();
var criteria = sheet.getRange(2,2,lastRow-1).getValues();
var data = sheet.getRange(2,1,lastRow-1).getValues();
var outData = ;
for (var i in data) {
if (data[i] == criteria[i]) {
outData.push(criteria[i])
}
else if (data[i] == ‘’) {
outData.push(criteria[i])
}
else {
outData.push(data[i])
}
}
sheet.getRange(2,2,outData.length).setValues(outData);
}
Setup:
Original data (from your ARRAYFORMULA) in A, storage in B. A button to assign the script.
Test cases:
Update all column A to column B when column B is empty
Remove some from A, they won’t be updated to column B
Update some from A, they will be updated to column B
Ok I understand, First I’m trying this with the button but nothing happens, I have copied the script you gave me and inserted a button to call the copy_rows function. I have my columns like this
Column A Column B
A
B
C
D
After pressing the button it says “Running Script” and then “Finished Script” but nothing happens in column B
I’m sure I’m doing something wrong, maybe I should change something in the script?
Slight variation on this question: I have two columns of data:
Player A. Player B
Peter. Paul
Through my app, the user is Player A and he/she adds Player B (Player A now “follows” Player B). Can your script be modified to automatically add a row that looks like this:
Player A. Player B
Paul. Peter
So, whenever a user “follows” another user, we automatically create a reciprocal relationship. The resultant sheet would look like this:
