Column A after clear:

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?

CodePudding user response：

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

Output

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.

CodePudding user response：

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)

}