Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Collect values from a column, clear the column and copy those values to another column

Column A after clear:

A
a
b
c
d

I need to get the values from a column before clearing that column, but I can only copy the values after clearing:

function lista_de_episodios_rc() {
  var page = 'copy';
  const ss = SpreadsheetApp.getActive().getSheetByName(page);
  
  var history = ss.getRange(page + '!A1:A');

  var to_del = 'A1:A'
  ss.getRange(to_del + ss.getMaxRows()).clear({contentsOnly: true, skipFilteredRows: true});

  history.copyTo(ss.getRange(page + '!C1'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
}

In this attempt, column C remains empty.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

Is there a way to store these values for use after cleaning?

>Solution :

There are many ways to do this. Here is quite a manual but working approach:

You can use getValues() and setValues() to get the values from the source column and set it in the target column.

function move() {
  Logger.log("Starting script...")

  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Range for 4 values in the first column
  const sourceColumn = ss.getRange(1, 1, 4, 1);
  // Range for 4 values in the second column 
  const targetColumn = ss.getRange(1, 2, 4, 1);

  // get values from source 
  const col1Content = sourceColumn.getValues();
  // clear the source column
  sourceColumn.clear();
  // now set the target values
  targetColumn.setValues(col1Content);

  Logger.log("Done.")
}

Input

A B
a
b
c
d

Output

A B
a
b
c
d

For other approaches I suggest you look at the documentation for Range class which describes a lot of other methods you could use to move the content. There are also different versions of copy() and clear() which allow you to specify whether you want to keep formats, data validation etc. or not.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading