Updating a field based on contents on another tab

问题:

I've built a spreadsheet with multiple tabs.

My 'Source' Tab 1 contains columns 'Id' and 'Name' My 'Target' Tab 2 contains 'Id'

I'm attempting to iterate through the rows in my Target Tab and read the value of the Id and then search my Source Tab for the Id. When I find the Id, I want to grab the value in the 'Name' field and add it to a cell on the same line of my Target Tab's Id.

I'm having a hard time working through the logic of iterating through one array and ensuring I find the value in either the Target tab or an array of the Target Tabs contents. In the Target tab, there may be more than one occurrence of an Id, and I would need to update them all.

Any suggestions would be welcome!

回答1:

here is a suggestion :

/* let us say source array with name(columnA) & ID(columnB) is array 'source'
and target array with only IDs is array 'target', you get these with something like*/
    var source = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getDataRange().getValues();
// and
    var target = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1].getDataRange().getValues();// if other columns, change index values in the arrays : 0=A, 1=B ...
//   then let's create a 3 rd array that will be the new target with ID + names, and call it 'newtarget' 
    var newtarget=new Array()
// the iteration could be like this :
          for(i=0;i<target.length;++i){ // don't miss any ID
           for(j=0;j<source.length;++j){ // iterate through source to find the name

             if(target[i][0].toString().match(source[j][1].toString()) == source[j][1].toString()){
               var newtargetrow=[source[j][0],target[i][0]] // if match found, store it with name (idx0) and ID (idx 1)
                   }else{
               var newtargetrow=['no name found',target[i][0]] // if no match, show it in name column
                   }
             newtarget.push(newtargetrow);// store result in new array with 2 columns
               } //loop source
         } // loop target

        /* now you have a newtarget array that can directly overwrite the old target 
        using setValues() */
        var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];// assuming the target sheet is sheet nr2
        sh.getRange(1,1,newtarget.length,newtarget[0].length).setValues(newtarget);
    //

note I didn't test this code but it should give you a starting point. I made comparison on strings with a .match but you could use other comparisons (direct equality between array elements)... you could also remove spaces in IDs if there is a risk of undesired spaces in the sheet data... I don't know your data so it's up to you.