# Formula to copy values from column to column

**URL:** <https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248>\
**Category:** Ask for Help\
**Created:** [April 30, 2020, 9:24pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248 "2020-04-30T21:24:36Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [April 30, 2020, 9:24pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/1 "2020-04-30T21:24:36Z")

</div>

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:

```auto
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!

---

<div class="post-metadata">

**Author:** ![Rohan\_Dayanand](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rohan_dayanand/32/1398_2.png) [@Rohan\_Dayanand](https://community.glideapps.com/u/Rohan_Dayanand)\
**Post date:** [April 30, 2020, 9:37pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/2 "2020-04-30T21:37:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 1, 2020, 12:52am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/3 "2020-05-01T00:52:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 1:05am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/4 "2020-05-01T01:05:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 1, 2020, 1:57am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/5 "2020-05-01T01:57:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 2:13am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/6 "2020-05-01T02:13:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 1, 2020, 3:01am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/7 "2020-05-01T03:01:24Z")

</div>

Thank you! I will definitely keep trying

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 3:22am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/8 "2020-05-01T03:22:00Z")

</div>

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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/a/ad6c64423ae6afe56ef8b36d33931d3405f3c8bc.png)

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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/4/482d4adb051fd06006e6646e2ec30ce87ca96175.gif)

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 1, 2020, 2:51pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/9 "2020-05-01T14:51:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 3:21pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/10 "2020-05-01T15:21:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 1, 2020, 4:06pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/11 "2020-05-01T16:06:07Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 4:14pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/12 "2020-05-01T16:14:17Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/e/e6f746c2da103c50e7d447f6d1cedf6fa7b2cd8e.png)

Choose “Add trigger”:

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/e/e3040437ba472085ea2ce9f31fdc6e6a6b9402c8.png)

Then edit the parameters as you wish:

![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/b0e0029d0bf9f3b69ba955329765a6ea8ed626b3.png)

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 1, 2020, 4:44pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/13 "2020-05-01T16:44:51Z")

</div>

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](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/8/8f9ea6e3fef71633cef38442b63f81a4531c095c.png)

is correct the script?

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 1, 2020, 11:16pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/14 "2020-05-01T23:16:45Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 2, 2020, 12:28am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/15 "2020-05-02T00:28:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 2, 2020, 12:47am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/16 "2020-05-02T00:47:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![edbs\_India](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/edbs_india/32/4055_2.png) [@edbs\_India](https://community.glideapps.com/u/edbs_India)\
**Post date:** [May 2, 2020, 1:27am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/17 "2020-05-02T01:27:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [May 2, 2020, 2:18am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/18 "2020-05-02T02:18:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlfredoZGC](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/alfredozgc/32/4958_2.png) [@AlfredoZGC](https://community.glideapps.com/u/AlfredoZGC)\
**Post date:** [May 2, 2020, 2:20am UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/19 "2020-05-02T02:20:42Z")

</div>

> [@ThinhDinh](#):
>
> No worries Alfredo, just message me or send an email to [ariesarsenal@gmail.com](mailto:ariesarsenal@gmail.com) when you need my help! Have a nice weekend.

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

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [December 30, 2020, 4:13pm UTC](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248/20 "2020-12-30T16:13:34Z")

</div>

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:

Player A. Player B  
Peter. Paul  
Paul. Peter

Thank you

[Next page](https://community.glideapps.com/t/formula-to-copy-values-from-column-to-column/8248.md?page=2)