CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user1783229
user1783229

Reputation: 193

copy data to a sheet from another based on matching values

This is a little different than my last question, but am unable to transfer the code for that to this.

Sheet 1 has individual contacts with company name. Each individual has one name, but company name may show up several times based on how many individuals in the sheet. So:

Name        Company       Work Address
John Smith  ABC Company
Jim Smith   XYZ Company
Bob Smith   ABC Company

Sheet 2 has Companies with their work address:

Company        Work Address
ABC Company    1234 Street, USA
XYZ Company    5678 Street, USA

How do I script so that the company addresses go over to the individual contact sheet?

I've tried using arrays, and searching for row/col indexes, but I am missing something. This code has got me the closest, but can't quite make it work (The code column and row numbers don't match with the sample, they are just the current arrays I am trying to work on, fyi):

function populateCofieldsincontact() {
  var writeSheet = sskey.getSheetByName('POCs');
  var sourceSheet = sskey.getSheetByName('Businesses');

  var writeData = writeSheet.getRange(1, 6, writeSheet.getLastRow(), 5).getValues();Logger.log(writeData)
  for (var w = 0; w < writeData.length; w++ ) {

    var sourceData = sourceSheet.getRange(1, 2, sourceSheet.getLastRow(), 3).getValues();
    var dest = [];
    for (var i = 0; i < sourceData.length; i++ ) {
      if (writeData ==sourceData[i][0].toString().match(writeData)) {
        dest.push(sourceData[i][2]); 
      }
    }
  }
  if (dest.length > 0 ) {
      writeSheet.getRange(2,10,dest.length,1).setValue(dest.toString());
    }
}

Any help greatly appreciated!

quick update - I am able to get the setValue (not setValues that won't work for some reason) and write the addresses in the correct column, but only way I could think to get it write in loop was to use getLastRow, and I only know how to do that for the sheet. So I wind up having all the addresses in the correct column, but written in rows starting at the bottom of where the actual data is. I can't figure out how to get it to write into the top row, then downward, ugh.

FINAL (hopefully) Code: I inserted a [w][0] next to the second mention of writeData on line 13 of @Adam's code and it seems to be working after a few tests. Thanks @Adam and @Serge!!:

function populateCofieldsincontact() {
  var writeSheet = sskey.getSheetByName('POCs');
  var sourceSheet = sskey.getSheetByName('Businesses');

  var writeData = writeSheet.getRange(2, 6, writeSheet.getLastRow() - 1).getValues();
  var sourceData = sourceSheet.getRange(2, 2, sourceSheet.getLastRow(), 12).getValues();
  var dest = [];
  var temp;

  for (var w = 0; w < writeData.length; w++) {
    temp = null;
    for (var i = 0; i < sourceData.length; i++) {
      if (writeData[w][0] == sourceData[i][0].toString().match(writeData[w][0])) {
        temp = sourceData[i][11];
        break;
      }
    }
    dest.push([temp]);
  }
  if (dest.length > 0 ) {
      writeSheet.getRange(2, 13, dest.length, 1).setValues(dest);  Logger.log(dest)
  }
}

Upvotes: 2

Views: 2780

Answers (2)

AdamL
AdamL

Reputation: 24599

See if this works as expected, hopefully my assumptions are correct (edit: typo corrected as per comment): 

function populateCofieldsincontact() {
  var writeSheet = sskey.getSheetByName('POCs');
  var sourceSheet = sskey.getSheetByName('Businesses');

  var writeData = writeSheet.getRange(2, 6, writeSheet.getLastRow() - 1).getValues();
  var sourceData = sourceSheet.getRange(1, 2, sourceSheet.getLastRow(), 3).getValues();
  var dest = [];
  var temp;

  for (var w = 0; w < writeData.length; w++) {
    temp = null;
    for (var i = 0; i < sourceData.length; i++) {
      if (writeData[w][0] == sourceData[i][0].toString().match(writeData[w][0])) {
        temp = sourceData[i][2];
        break;
      }
    }
    dest.push([temp]);
  }
  if (dest.length > 0 ) {
      writeSheet.getRange(2, 10, dest.length, 1).setValues(dest);
  }
}

I guess there are a few issues with your code, but I think the important message (which took a noob like me a fair while to work out) is that when you use getValues and setValues, you are always dealing with 2-dimensional arrays, even if the array is only one column wide. Hence the square brackets in the dest.push([temp]) bit - you are actually progressively pushing an array with a length of one onto the dest array.

Upvotes: 4

Serge insas
Serge insas

Reputation: 46794

You should use two loops and iterate the second loop in each step of the first one. If no match is found in the second loop then you should push an empty item in the first array to keep the data alignment in the first data sheet. I'm sure you can go through this...

Upvotes: 0

Related Questions