CODEWITHSUNDEEP
google-apps-scriptmatchgoogle-sheets-api
Dennis
Dennis

Reputation: 67

Validating specific string in Google App Script

Situation:

Hi all! I have a Google Spreadsheet which contain two tabs ("Today" and "Current") and I want to validate the values in the cell of last row and column C in "Today" tab (such as C50 or C100, the row number may change) is it equal to the string of "ERROR". If this cell contain the string "ERROR", it will copy the data in the cell of A2:J2 from "Current" tab and replace the "ERROR" string in "Today".


Problem:

Hence, I typed the following code and now it can get the values in "Today" but it failed to validate whether it contains "ERROR" or not. May I know what's wrong with my code? Thank you!


Code:

function checkdata(){
     var ss = SpreadsheetApp.getActiveSpreadsheet()
     var sheet = ss.getSheetByName('Today');
     var lastRow = sheet.getLastRow();
     var lastColumn = sheet.getLastColumn();
     var data = sheet.getSheetValues(lastRow,3,1,1);
     var ss2 = SpreadsheetApp.getActiveSpreadsheet();
     var sheet2 = ss2.getSheetByName('Current');  
     var values = sheet2.getRange("A2:J2").getValues()[0];  

  if (data) {
    if (data.indexOf('#ERROR!') < 0) {
  sheet.getRange(lastRow+1, 1, 1, 10).setValues([[].concat(values)]); 
    }}}

Upvotes: 0

Views: 364

Answers (2)

Tanaike
Tanaike

Reputation: 201603

Modification point:

  • getSheetValues() returns 2 dimensional array. In this script, the if statement is always true. I thought that this might be the reason of your issue.

Modified script:

From:

var data = sheet.getSheetValues(lastRow,3,1,1);

To:

var data = sheet.getRange(lastRow, 3).getValue();
  • getValue() returns an object which is not an array. For example, when the value of cell is the string value, the string value is returned. By this, I think that the if statement works.

References:

Upvotes: 2

ziganotschka
ziganotschka

Reputation: 26836

May I know what's wrong with my code?

A few issues:

  1. If you want to replace the ERROR string, you need to copy the data from Current into the row where ERROR is located (the last row), not the row beneath it. So: sheet.getRange(lastRow, 1, 1, 10).setValues([[].concat(values)]);
  2. #ERROR! and ERROR is not the same, so review your situation to decide which is the string you are looking for (if your cell content is #ERROR!, you can find ERROR within it, but not the opposite
  3. sheet.getSheetValues(lastRow,3,1,1); will return you a 2-D value range. In order to use the method indexOf, you need to address a single value of this value range, e.g. data[0][0].indexOf Alternatively: Define var data = sheet.getRange(lastRow,3,1,1).getValue(); which will directly return you the value, not the value range
  4. The method indexOf is defined in such a way that it return -1 if the value is not found and the position of the value (which is bigger than -1) if the value is found. So, you need to change indexOf('#ERROR!') < 0 to indexOf('#ERROR!') >= 0

Sample:

function checkdata(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName('Today');
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var data = sheet.getSheetValues(lastRow,3,1,1);
  var sheet2 = ss.getSheetByName('Current');  
  var values = sheet2.getRange("A2:J2").getValues()[0];  

  if (data) {
    if (data[0][0].indexOf('#ERROR!') >= 0) {
      sheet.getRange(lastRow, 1, 1, 10).setValues([[].concat(values)]); 
    }}}

Upvotes: 0

Related Questions