In Google Sheets how to scan entire column(s) for error cells and replace content with content from-CodePudding

I have a sheet that's recording new rows at the bottom with new values every hour or so. Sometimes the API call gets some error. I changed the error message for loading… but I would still like for there to be a way to filter out these errors from the columns and replace this values with the values taken from the cell just above, if this one has no error.

So this is an example of the cells with the errors:

This is how the array looks like retrieved with the code I used below:

I started with this:

function deleteErrors () {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CELL_ERRORS");
let checkRange = sheet.getRange(1, 1, sheet.getLastRow(), 2).getDisplayValues();
let rangeReversed = checkRange.reverse();
console.log(rangeReversed);
}

How can I remove the "Loading…" and replace it with the value of the cell just above IF the cell above has no error, or in the case of the array of arrays with the value below since I used reverse. I used reverse because the idea is to filter from the bottom as the rows are being recorded on the bottom.

So this is how it should look after the correct script is applied:

And the array of arrays should like like this:

But this array of arrays is not that important I guess. Basically I just need for the cells with the Loading… error to be overwritten with the value of the cell just above, if that cell above does not have also Loading… of course.

Is this possible? Maybe there is some way of using a formula for this but I prefer to use Apps Script.

I tried something like this:

function deleteErrors () {

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CELL_ERRORS");
let checkRange = sheet.getRange(1, 1, sheet.getLastRow(), 2).getDisplayValues();
let rangeReversed = checkRange.reverse();
console.log(rangeReversed);

// Get row numbers of cells on col 1 with errors
let cellsCol1 = sheet.getRange(1, 1, sheet.getLastRow(), 1).createTextFinder("Loading…").findAll().map(x => x.getRowIndex());
console.log(cellsCol1);

// Get row numbers of cells on col 1 above the ones with error (hopefully these won't have errors!)
let okCellsCol1 = cellsCol1.map(x => x - 1);
console.log(okCellsCol1);

// Get row numbers of cells on col 2 with errors
let cellsCol2 = sheet.getRange(1, 2, sheet.getLastRow(), 2).createTextFinder("Loading…").findAll().map(x => x.getRowIndex());
console.log(cellsCol2);

// Get row numbers of cells on col 2 above the ones with error (hopefully these won't have errors!)
let okCellsCol2 = cellsCol2.map(x => x - 1);
console.log(okCellsCol2);

}

It's not working but how can I go from here?

My file: https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

CodePudding user response：

You want to achieve the following situation. (Those images are from OP's question.)
- From
- To

In this case, how about the following modified script?

Modified script:

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CELL_ERRORS");
let checkRange = sheet.getRange(1, 1, sheet.getLastRow(), 2).getDisplayValues();

// I modified below script.
const values = checkRange.map(([a, b], i, ar) => {
  if (i > 0) {
    return [a == "Loading…" ? ar[i - 1][0] : a, b == "Loading…" ? ar[i - 1][1] : b];
  }
  return [a, b];
});
console.log(values)

Note:

In this modified script, when the 1st row includes Loading…, Loading… is used. If you want to replace this with others, please tell me.

Reference:

map()

Added:

About How can we overwrite the values back onto the cells? and How can we overwrite the values back onto the cells? Also in my main file I have 21 columns. This will work for 2 columns. If I add the 3rd it won't do the same for the 3rd column in the array. Finally if 2 cells in a row have error (one on top of the other), the Loading… won't disappear. in the comment, I updated the above script as follows.

Modified script:

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CELL_ERRORS");
let checkRange = sheet.getRange(1, 1, sheet.getLastRow(), 3);
const currentValues = checkRange.getDisplayValues();
const transposed = currentValues[0].map((_, c) => currentValues.map(r => r[c]));
const transposedValues = transposed.map(row => {
  let temp = "";
  return row.map((e, i) => {
    if (i > 0) {
      if (e == "Loading…") {
        return temp;
      } else {
        temp = e;
      }
    } else {
      temp = e;
    }
    return e;
  });
});
const values = transposedValues[0].map((_, c) => transposedValues.map(r => r[c]));
console.log(values)
checkRange.setValues(values);

In this modification, I think that your additional situations might be filled.