CODEWITHSUNDEEP
google-apps-script
Digital Farmer
Digital Farmer

Reputation: 2107

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.

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

Upvotes: 0

Views: 32

Answers (2)

NEWAZA
NEWAZA

Reputation: 1630

Here's a function to get all values in a column, and move them to another column:

function lista_de_episodios_rc() {

  const fromCol = `A`
  const toCol = `C`

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`copy`)
  const range = sheet.getRange(`${fromCol}:${fromCol}`)
  const values = range.getValues().filter(cell => cell !== ``)
  
  range.clearContent()
  sheet.getRange(`${toCol}1:${toCol}${values.length+1}`).setValues(values)

}

Upvotes: 1

Mushroomator
Mushroomator

Reputation: 9168

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.

Upvotes: 1

Related Questions