Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-macros

How to set a value in the cells of the next column?


I have table with hundred of text entries.

I want to tag those entries according to the original text.

Eg:

TEXT (A)         TAG (B)
Facebook       Social Media
Instagram      Social Media
Stackoverflow  Good Site

I am using the code below.

But this only copies the column A into column B !!

 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getRange('A2:A5');
 var data = range.getValues();
 var tag = sheet.getRange('B2:B5');

  for (var i = 0; i<data.length; i++) 
  {
      if(String(data[i][1]).match(/facebook|instagram/gi))
      {
        data([i][1]='Social Media');
      }
      if(String(data[i][1]).match(/Stack/gi))
      {
        data([i][1]='Good Site');
      }

 }
  tag.setValues(data); 
}

The current result :

TEXT (A)        TAG (B)
Facebook       Facebook    
Instagram      Instagram       
Stackoverflow  Stackoverflow  

Solution

  • Try this:

    function myfunction() { 
      var sheet = SpreadsheetApp.getActiveSheet();
      var range = sheet.getRange('A2:A5');
      var data = range.getValues();
      var tag = sheet.getRange('B2:B5'); 
      var vA= tag.getValues();
      for (var i = 0; i<data.length; i++) {
        if(String(data[i][0]).match(/facebook|instagram/gi)) {
          vA([i][0]='Social Media');
        }
        if(String(data[i][0]).match(/Stack/gi)) {
          vA([i][0]='Good Site');
        }   
      }
      tag.setValues(vA); 
    }