CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-api
Hasinthaka Tharindu
Hasinthaka Tharindu

Reputation: 23

Find Google Sheets that matches a value in a cell of a sheet and execute a piece of code

I am sorry for this hideouts looking question but I have to ask. So bare with me.

I am using google app script and trying to achieve following. I have two google spreadsheet books.

1.) Book A has only one sheet.
2.) Book B has 10 sheets.



1.) In Book A's sheet column A contains range of text values. Ex: ALBWA ,AUSCE, AUSLN , BFLCB
2. In Book B there are multiple sheets named according to the values in Book A's sheet. Ex : Book B, Sheet 1 Name = ALBWA , Sheet 2 Name = AUSCE etc... 3. Inside all these Sheets in same Cell ("G3") has a value.

The thing I am trying to achieve is: Using a script get all the text values of Book A's Sheet and search for the Sheet in Book B. If a Sheet is found then get "G3" value and set it in the column B of Book A in front of the matching text. If Sheet "ALBWA" "G23" has a text called "ABC" then it should be pasted to Book A's sheet in column B like A1 = ALBWA and B1 should be "ABC". This needs to be repeated until all the paged are covered.

I know I need to use for loop and I am trying my level best. i managed to get it work but it only gets the last value and return only the last sheet and value. Code is below.

function testSplit(){

var gSheet            = SpreadsheetApp.openById("1q0tZMrvCvrH4dsQ0zx8GqI8mqZUsLcEGKu3IHfVrJ2M");
var sheet             = gSheet.getSheetByName("RS_Tharalsdson");
  
  var range_input     = sheet.getRange("G2:G").getValues();
  var filtered_input  = range_input.filter(String); //Eliminating all the empty values
  
  var ss          =SpreadsheetApp.getActive();
  var shA         =ss.getSheets();
  
    for(i=1;i<=filtered_input.length;i++){
            for(var j=1;j<shA.length;j++){
              var txt         = sheet.getRange(1+i,7).getValue(); //Get the values of each cell individually
              var sheetName = shA[j].getName();                
                  if(txt==sheetName){
                    console.log(sheetName)
                    var sheet_1     = ss.getSheetByName(sheetName);
                    var cell        = sheet_1.getRange(3,7);
                    var allVals     = cell.getValues();
                    var strVal      =  allVals.toString();
              
  
                    //Only get the Time from the text in Tharaldson Group
  
                    var array1  = [{}];
                    var string1 = strVal;
                    array1      = string1.split(":");      
                    var ddLine  = array1[3]+":"+array1[4];
                    
                    //Pasting the deadline to the relevant cell of the target sheet

                    sheet.getRange(1+i,9).setValue(ddLine);
                    console.log("Success");
   }
  }
 }
}

If some kind person can help me out with figuring out this I will be so grateful. Thank you so much in advance.

Upvotes: 1

Views: 685

Answers (1)

RayGun
RayGun

Reputation: 521

The below script accomplishes the following:

  1. Collect the sheet names from the range you specify in book A
  2. Find each collected sheet in book B
  3. Get the value in the range you specify for each sheet in book B
  4. Paste the value from 3 into the paste range you specify in book A

Working Example: Book A Book B

const bookAId = '1wq1FCct3nB8oiVebYf__4NqAC0wtDSA_sBDPaPG67Ek'; // ssId of book A
const bookBId = '1tx6k1TZU4h3ZrUZLFvoh3CvggFpvbLYNhiaWS0_rvmA'; // ssId of book B
const sheetA = 'Sheet1' // name of sheet in book A containing sheet names
const valueRange = 'G1'; // the cell in book B sheet i that you want to copy

function testSplit() {
  const ssA = SpreadsheetApp.openById(bookAId);
  const sA = ssA.getSheetByName(sheetA);
  const sheetNames = sA.getRange('A2:A').getValues().reduce((names, row) =>  row[0] !== '' ? names.concat(row[0]) : names ,[]);
  const ssB = SpreadsheetApp.openById(bookBId);
  const valuesFromSheetB = []; // collect the values you find in each sheet of book B
  for (const sheetName of sheetNames) {
    const sheet = ssB.getSheetByName(sheetName);
    if (!sheet) {
      valuesFromSheetB.push(['Sheet Not Found']);
      continue;
    }
    const value = sheet.getRange(valueRange).getValue(); // get the value from the range you specified
    valuesFromSheetB.push([value]);
  }
  sA.getRange(2, 2, valuesFromSheetB.length, 1).setValues(valuesFromSheetB); // paste all of the values you collected into the paste range you specified in book A
}

Note that one key difference between this script and yours is that I am collecting all of the needed values from sheets in book B before pasting them into the paste range, as is recommend by Google for minimizing costly calls to the spreadsheet service.

Upvotes: 2

Related Questions