Get values skip errors

Question

I‘m currently learning Google app scripts and encounter a small problem.

Background: I‘m fetching data from an API and write it into a spreadsheet, which is mostly working fine, and then do some work with the data.

In some cases the import is not working for what ever reason. (Cell value: #ERROR!) For that cases I made a „copy“ function to copy the API data to another sheet,skipping errors. So if 1 import is broken I can work with the Last successfull one.

Problem: For one sheet the runtime was too high so I changed from this code:

function copy() {
  var copyWert = ""; 
  for (var i = 3; i < 247; i++) { 
    for (var j = 1; j < 4;j++) {
      copyWert = SpreadsheetApp.getActive().getSheetByName('API').getRange(i, j).getValue();
      if (copyWert == "#ERROR!" || copyWert == "" || copyWert == "#NAME!") {
        
      } else {
        SpreadsheetApp.getActive().getSheetByName('Copy').getRange(i, j).setValue(copyWert);
      }
    }
  }
}

To:

function copy() {
  var values = SpreadsheetApp.getActiveSheet().getRange("A2:D300").getValues();
  SpreadsheetApp.getActive().getSheetByName('Copy').getRange("A2:D300").setValues(values);
}

Which is way faster,but missing the check. Any ideas?

Try this:`function copy1() { const ss=SpreadsheetApp.getActive(); const sh=ss.getSheetByName('API'); const rg=sh.getRange(3,1,245,4); var vA=rg.getValues(); for(var i=0;i — Cooper, Sep 09 '20 at 23:26

score 2 · Accepted Answer · answered Sep 09 '20 at 23:28

I believe your goal as follows.

You want to copy the cell values from API to Copy when the value is not "#ERROR!", "" and "#NAME!".
You want to achieve this using Google Apps Script.

Modification points:

When you want to copy only the cell values which have no "#ERROR!", "" and "#NAME!", in order to reduce the process cost, I would like to use Sheets API.
- In this case, the cell values which have "#ERROR!", "" and "#NAME!" are not overwritten to the existing cells in Copy.
When you can permit the overwrite the all cells, I think that your 2nd script might be used by modifying.

Pattern 1:

In this pattern, when you want to copy only the cell values which have no "#ERROR!", "" and "#NAME!", in order to reduce the process cost, I would like to use Sheets API.

Sample script:

Before you use this script, please enable Sheets API at Advanced Google services.

function copy() {
  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };
  
  var spreadsheet = SpreadsheetApp.getActive();
  var values = spreadsheet.getSheetByName('API').getRange("A2:D300").getValues();
  var data = values.reduce((ar, r, i) => {
      r.forEach((copyWert, j) => {
        if (!(copyWert == "#ERROR!" || copyWert == "" || copyWert == "#NAME!")) {
          ar.push({range: `'Copy'!${columnToLetter(j + 1)}${i + 2}`, values: [[copyWert]]});
        }
      });
      return ar;
  }, []);
  Sheets.Spreadsheets.Values.batchUpdate({data: data, valueInputOption: "USER_ENTERED"}, spreadsheet.getId());
}

Pattern 2:

In this pattern, your 2nd script is modified.

Sample script:

function copy() {
  var spreadsheet = SpreadsheetApp.getActive();
  var values = spreadsheet.getSheetByName('API').getRange("A2:D300").getValues();
  values = values.map(r => r.map(copyWert => (copyWert == "#ERROR!" || copyWert == "" || copyWert == "#NAME!") ? "" : copyWert));
  spreadsheet.getSheetByName('Copy').getRange("A2:D300").setValues(values);
}

References:

In above scripts, please use the script with enabling V8 at the script editor. — Tanaike, Sep 09 '20 at 23:36
I just realised, that this script work, but is replacing the error value with "" in the array. Which still overrides the old value in the Copy sheet. — Dewx19, Sep 11 '20 at 08:10
@Dewx19 Thank you for replying. I apologize for the inconvenience. I would like to confirm your current situation. 1. Which pattern are you using? 2. Can you provide the sample Spreadsheet for replicating your issue? By this, I would like to confirm it. Because in my environment, I cannot replicate your situation. I apologize for this. If you can cooperate to resolve your issue, I'm glad. — Tanaike, Sep 11 '20 at 08:13
Hey @Tanaike I cant provide any sheet but can give you an example: Api looks like 1123 test #ERROR! asdf Copy looks like (before skript run): 1123 test asd asdf Copy after skript run: 1123 test asdf — Dewx19, Sep 11 '20 at 08:38
@Dewx19 Thank you for replying. I could understand that you cannot provide the sample Spreadsheet. When you show the samples, please add them to your question by including more information. Because unfortunately, I cannot understand about `Api looks like 1123 test #ERROR! asdf Copy looks like (before skript run): 1123 test asd asdf Copy after skript run: 1123 test asdf`. I apologize for this. And, can I ask you about which pattern of my sample scripts you are using? Unfortunately, I'm not sure about the script you are using. I deeply apologize for my poor English skill. — Tanaike, Sep 11 '20 at 08:42

Get values skip errors

1 Answers1

Modification points:

Pattern 1:

Sample script:

Pattern 2:

Sample script:

References: