Google Sheets Script - Copy formulas to values based on column-CodePudding

I want to be able to replace/paste all or some cells in the row from formulas to values based on a column cell value.

If ColA = "Removed" then all/some cells right copy and paste as value.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
  var nextCell0 = range.offset(0, 0);
  var nextCell1 = range.offset(0, 1);
  if( nextCell0.getValue() === 'Removed' ) {
    nextCell1.copyTo(nextCell0,{contentsOnly: true});
  }
}

This code did not work. Basically, ColA just get copied with exact values from ColB

Screenshot examples:

Before script

After script execution

Multiple Columns to convert from formula to value

Before Script

After script execution

Thanks!

CodePudding user response：

Modification points:

In your script, nextCell0 is the same range as range. And, nextCell0.getValue() returns the value from the 1st cell. In this case, it's "A1". And, nextCell1.copyTo(nextCell0, { contentsOnly: true }) copies from column "B" to column "A". So, when the cell "A1" is "Removed", column "B" is copied to column "A". I thought that this is the reason for your issue.
sheet.getRange(1, 1, sheet.getMaxRows(), 1) returns all rows in the sheet. This has already been mentioned in TheMaster 's comment. In this case, sheet.getLastRow() might be suitable.
From I want to be able to replace/paste all or some cells in the row from formulas to values based on a column cell value. If ColA = "Removed" then all/some cells right copy and paste as value., unfortunately, I couldn't understand your actual Spreadsheet. So, in this answer, I would like to propose the following patterns.

In order to remove this issue, how about the following modification?

Pattern 1:

In this pattern, it supposes that both columns "A" and "B" have the formulas.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var formulas = range.getFormulas();
  var values = range.getValues().map(([a], i) => [a == 'Removed' ? formulas[i][1] : formulas[i][0]]);
  range.offset(0, 0, values.length, 1).setFormulas(values);
}

When this script is run, when column "A" is "Removed", the formula of column "B" with the same row is copied to column "A".

Pattern 2:

In this pattern, it supposes that the column "A" and "B" have the values without formulas and the formulas, respectively.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var formulas = range.getFormulas();
  var values = range.getValues().map(([a], i) => [a == 'Removed' ? formulas[i][1] : a]);
  range.offset(0, 0, values.length, 1).setValues(values);
}

When this script is run, when column "A" is "Removed", the formula of column "B" with the same row is copied to column "A".

Pattern 3:

In this pattern, it supposes that the column "A" and "B" have the formulas and the values without formulas, respectively.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var formulas = range.getFormulas();
  var values = range.getValues().map(([a, b], i) => [a == 'Removed' ? b : formulas[i][0]]);
  range.offset(0, 0, values.length, 1).setValues(values);
}

When this script is run, when column "A" is "Removed", the value of column "B" with the same row is copied to column "A".

Pattern 4:

In this pattern, it supposes that both columns "A" and "B" have values without formulas.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var values = range.getValues().map(([a, b]) => a == 'Removed' ? [b, b] : [a, b]);
  range.setValues(values);
}

When this script is run, when column "A" is "Removed", the value of column "B" with the same row is copied to column "A".

References:

Added:

From your added images, how about the following sample script?

Sample script:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2);
  var formulas = range.getFormulas();
  var values = range.getValues().map(([a, b], i) => [a == 'Removed' ? b : formulas[i][1]]);
  range.offset(0, 1, values.length, 1).setValues(values);
}

Answer for your 2nd question:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 5);
  var formulas = range.getFormulas().map(r => r.splice(1));
  var values = range.getValues().map(([a, ...b], i) => a == 'Removed' ? b : formulas[i]);
  range.offset(0, 1, values.length, 4).setValues(values);
}