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.
Advertisement
Answer
The below script accomplishes the following:
- Collect the sheet names from the range you specify in book A
- Find each collected sheet in book B
- Get the value in the range you specify for each sheet in book B
- 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.