Formula to copy values from column to column

Hello!

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

Please any help!

2 Likes

Would the Google Sheet IMPORTRANGE function help? https://support.google.com/docs/answer/3093340?hl=en

1 Like

It may function! I will give it a try, Is a very interesting function, I didn’t knew about it. Thank you very much!

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.
1 Like

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.

1 Like

Keep trying, I will try to help you if I got some time today. Good luck!

1 Like

Thank you! I will definitely keep trying

Hi, I have done it.

The script is:

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: image

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

ezgif-1-93575d7bfac7

2 Likes

@ThinhDinh I’m so sorry for my late reply. Thank you so much, I’ll try it right know! Thank you so much!

1 Like

No worries Alfredo, give it a try and tell me if it works!

@ThinhDinh I’m having problems, what is the value you asign to var outData?, I cant read it.

Also, is there any way to do this automatic? whitout a button?

Hi, it’s the combination of “[” and “]”.

There’s an automatic way by setting the schedule for the script.

Navigate to here and select “Current project’s trigger”:

Choose “Add trigger”:

Then edit the parameters as you wish:

image

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?

image

is correct the script?

1 Like

Maybe it’s the ss.getSheets()[0]. I took the first sheet in my file hence the [0] index. Change that to the index of the sheet you need?

Yes! that did it! you are a genious. Thank you! It works perfect!

1 Like

No worries Alfredo, just message me or send an email to ariesarsenal@gmail.com when you need my help! Have a nice weekend.

Done very nice I to helps me also. Is it possible to call this function from Glideapps ?

No, as far as I aware. You can schedule it anyway, just make it run every minute?

Thank you very much, I really appreciate your help! Have a nice weekend!

1 Like