CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Evan292
Evan292

Reputation: 13

Substring comparison across columns within Google Apps Script

I have a large dataset pasted into Google Sheets. I want to compare two columns in this sheet. The first column is filled with long strings, I only care about the first character in each cell in this column.

The second column is text-based. I want to paste data into a new sheet if the string in column A starts a number (not a letter) and the text in column B is "Early".

My challenge is using Javascript to get only the substring of 1 character for column A. The error is "TypeError: str.substring is not a function." I have also tried slice() as well to no effect. Is this not supported in Apps Script?

How can I isolate those first characters and then compare alongside the other column?

I also then need to push the rows that meet this criteria in both columns to a new tab.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('data');
  var str = sheet.getRange('A:A').getValues(); 
  var char = str.substring(0,2);
  var stage = sheet.getRange('B:B');
  var endColumn = sheet.getLastColumn();
  var value_stage = (stage.getValues());
  var value_char = (char.getValues());

  var csh = ss.getSheetByName('new_sheet'); //destination sheet
  var data = [];
  var j =[];
    for (i=0; i<value_char.length;i++) {
      if 
      (
        (
          ( value_char[i] == '0') ||
          ( value_char[i] == '1') ||
          ( value_char[i] == '2') ||
          ( value_char[i] == '3') || etc... )

       &&
          ( value_stage[i] == 'Early')
)

  data.push.apply(data,sheet.getRange(i+1,1,1,endColumn).getValues());
  j.push(i);
 }
 }
 csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);

SAMPLE DATA BELOW:

**ID**                                **Stage**  **Name**
A               texthere                Early   Bob
B               abcdefgh                Late    Sally
52              texthere                Early   Jim
C               thesdfas                Late    Allan
00              tsdfsdfd                Late    Susan
11              qqwerrww                Early   Ryan
Q               tsdfsagd                Early   Sarah
98              fdsafads                Early   Evan
09              fdasfdsa                Early   Paul
10              abcdefgh                New     Megan
10              abcdefgh                Early   Cooper





NOTE:  in the real dataset, columns A and B are actually K & L

And the ID column has those spaces between the early part of the string and the end. This is what I am trying to slice off

Upvotes: 0

Views: 280

Answers (1)

Cooper
Cooper

Reputation: 64040

Try this:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh=ss.getSheetByName('data');
var svs=sh.getRange(1,2,sheet.getLastRow(),2).getValues();
var csh=ss.getSheetByName('new_sheet');
var data=[];
var j=[];
svs.forEach(function(r,i){
  if((r[0].slice(0,1)=='1' || r[0].slice(0,1)=='2') && r[1]=='Early') {
  //do what you want in here
  }
});

Upvotes: 1

Related Questions