Move specific text from one cell to another in Google sheets using a script

Question

I have a sheet that has text entries in a particular column.

Each cell within that column has a number of words in it; I need to copy two or three of those words (various company names), based on their inherent value (not based on where they're positioned in the text string in the cell, nor based on what comes before or after them).

I want a function to copy those company names to the same row in another column in the sheet.

I'v been playing with this script, but I don't think it can cope with the variety in the inherent value, but perhaps it can and I just don't know the right way to implement it:

function onOpen() { 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [];
menuEntries.push({name: "Copy Company Name to Col J", functionName: 
"fillColJ"});
ss.addMenu("Move Data", menuEntries);            
}
function fillColJ() {
var s = SpreadsheetApp.getActiveSheet();
var data = s.getDataRange().getValues();
var data_len = data.length;
for(var i=0; i<data_len; i++) {
if(data[i][5] == "Company Name") {
  s.getRange(i+1,10).setValue("Company Name");
  }
}
}

Any help greatly appreciated!

Read about JavaScript's String methods, and also its Array methods. Both support accessing values inside the whole. — tehhowch

Zak Zak · Accepted Answer · 2018-05-20T11:32:04

You could use either match() or indexOf() to find the company names inside of the text in your cells.

Here's an example showing one way to solve the problem using match():

function fillColJ() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data  = sheet.getDataRange().getValues();

  var regex   = /Name 1|Name 2|Name 3/
  var matches = []; 

  for(i in data){
    // Try to match one of the company names in the regular expression
    // If the match fails, then return an array element containing an empty string
    matches.push(data[i][5].match(regex) || [""]);
  }

  sheet.getRange(1, 10, matches.length, 1).setValues(matches);
}

Move specific text from one cell to another in Google sheets using a script

1 Answers