在Google表格中将SetValues()设置为两个不同的单元格区域

BookB 我在BookA中存储了BookB的工作表名称.我想搜索BookB中与存储在BookA中的工作表名称匹配的所有工作表.如果找到工作表，请获取单元格"A3"中的值，并将其粘贴到相应工作表名称前面的BookA中.(我已经成功地完成了此任务.现在来发行.请大括号) 我想获取文件格式"详细信息，但BookB的工作表中没有重复的内容，并将其粘贴到页面名称前面的BookA的工作表中.可能是我的方式不正确.如果有人可以帮助我，我将不胜感激. 请注意，在给定的两张纸中，在两个不同的范围内提到了文件格式的详细信息.ALBW-D6:D21和BFLCB-F6:F21 const pmsRange ='A3'//您要复制的B书I中的单元格函数getFileFormat(){const ssA = SpreadsheetApp.openById(bookAId);const sA = ssA.getSheetByName(sheetA);const sheetNames = sA.getRange('G2:G').getValues().reduce((names，row)=> row [0]！==''?names.concat(row [0]):名称，[]);const ssB = SpreadsheetApp.openById(bookBId);const valuesFromSheetB = [];//收集您在每本书B的表中找到的值for(const sheetName of sheetNames){const sheet = ssB.getSheetByName(sheetName);如果(！sheet){valuesFromSheetB.push([['Sheet Not Found']);继续;}const value = sheet.getRange(pmsRange).getValue();//从您指定的范围中获取值var array1 = [{}];var string1 = value;array1 = string1.split(/[:\ n]/);var pms = array1 [1];pms = pms.replace(/\ s +/g，``);if(pms.toLowerCase()=="onq"){console.log(sheetName +:" + pms);var col0 = exts.map(function(value，index){return value [0];});constdistinct =(value，index，self)=> {return self.indexOf(value)=== index;}var unq = col0.filter(distinct).toString();console.log(unq)extsFromSheetB.push([unq])}sA.getRange(2，8，valuesFromSheetB.length，1).setValues(valuesFromSheetB);//将收集到的所有值粘贴到在A书中指定的粘贴范围中}} 解决方案这些修改应该可以为您提供所需的内容.该脚本将在书籍B中找到其名称列在书籍A中的工作表.一旦找到工作表，它将检查该工作表的pmsRange中的值是否包含pmsSearchValue.如果是这样，则它将存储所有以'/'分隔的文件格式.如果没有，它将存储".最后，遍历从A书收集的每个工作表名称后，它将文件格式粘贴到您在示例中指定的粘贴范围. const pmsRange ='A3'//您要复制的B书I中的单元格const pmsSearchValue ='OnQ';const fileFormatCol = 4//列Dconst fileFormatRow = 6//包含文件格式的第一行函数getFileFormat(){const ssA = SpreadsheetApp.openById(bookAId);const sA = ssA.getSheetByName(sheetA);const sheetNames = sA.getRange('G2:G').getValues().reduce((names，row)=> row [0]！==''?names.concat(row [0]):名称，[]);const ssB = SpreadsheetApp.openById(bookBId);const fileFormatsFromBookB = [];//收集您在每本书B的表中找到的值for(const sheetName of sheetNames){const sheet = ssB.getSheetByName(sheetName);如果(！sheet)继续;const pmsCell = sheet.getRange(pmsRange).getValue();if(pmsCell&&pmsCell.indexOf(pmsSearchValue)){const fileFormatRange = sheet.getRange(fileFormatRow，fileFormatCol，sheet.getLastRow()，1);const fileFormats = fileFormatRange.getValues().filter(f => f！=='').join('/');fileFormatsFromBookB.push([fileFormats]);} 别的 {fileFormatsFromBookB.push(['']);}sA.getRange(2，10，fileFormatsFromBookB.length，1).setValues(fileFormatsFromBookB);//将收集到的所有值粘贴到在A书中指定的粘贴范围中}} 参考:无.这主要是香草JavaScript，它利用了Apps脚本电子表格类您已经在问题样本中使用了.I have two spreadsheet books.BookABookBI have sheet names of BookB stored in BookA.I want to search through all the sheets in BookB that matches the sheet name stored in BookA. If a sheet is found get the values in Cell 'A3' and paste it in BookA in front of the respective sheet name.(I have managed to achieve this task successfully. Issue comes now. Brace yourselves)I want to get the 'File Format' details without duplicates from the sheets of BookB and paste that in the sheet of BookA in front of the page name. May be my way is not correct. If someone can help I am grateful.Note that File Format details are mentioned in two different ranges in the given two sheets. ALBW - D6:D21 and BFLCB - F6:F21const pmsRange = 'A3' // the cell in book B sheet i that you want to copy function getFileFormat(){ const ssA = SpreadsheetApp.openById(bookAId); const sA = ssA.getSheetByName(sheetA); const sheetNames = sA.getRange('G2:G').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(pmsRange).getValue(); // get the value from the range you specified var array1 = [{}]; var string1 = value; array1 = string1.split(/[:\n]/); var pms = array1[1]; pms = pms.replace(/\s+/g, ''); if(pms.toLowerCase()=="onq"){ console.log(sheetName+":"+pms); var col0 = exts.map(function(value,index) { return value[0]; }); const distinct = (value, index, self) =>{ return self.indexOf(value)===index;} var unq = col0.filter(distinct).toString(); console.log(unq) extsFromSheetB.push([unq]) } sA.getRange(2, 8, valuesFromSheetB.length, 1).setValues(valuesFromSheetB); // paste all of the values you collected into the paste range you specified in book A } } 解决方案 These edits should get you what you need. The script will find sheets in book B whose names are listed in book A. Once a sheet is found, it will check to see if the value in the pmsRange of that sheet contains the pmsSearchValue. If it does, then it will store all of the file formats separated by ' / '. If it doesn't then it will store ''. Finally, after iterating over every sheet name collected from book A, it will paste the file formats into the paste range that you specified in your example.const pmsRange = 'A3' // the cell in book B sheet i that you want to copyconst pmsSearchValue = 'OnQ';const fileFormatCol = 4 // column Dconst fileFormatRow = 6 // first row containing file formatsfunction getFileFormat(){ const ssA = SpreadsheetApp.openById(bookAId); const sA = ssA.getSheetByName(sheetA); const sheetNames = sA.getRange('G2:G').getValues().reduce((names, row) => row[0] !== '' ? names.concat(row[0]) : names ,[]); const ssB = SpreadsheetApp.openById(bookBId); const fileFormatsFromBookB = []; // collect the values you find in each sheet of book B for (const sheetName of sheetNames) { const sheet = ssB.getSheetByName(sheetName); if (!sheet) continue; const pmsCell = sheet.getRange(pmsRange).getValue(); if (pmsCell && pmsCell.indexOf(pmsSearchValue)) { const fileFormatRange = sheet.getRange(fileFormatRow, fileFormatCol, sheet.getLastRow(), 1); const fileFormats = fileFormatRange.getValues().filter(f => f !== '').join(' / '); fileFormatsFromBookB.push([fileFormats]); } else { fileFormatsFromBookB.push(['']); } sA.getRange(2, 10, fileFormatsFromBookB.length, 1).setValues(fileFormatsFromBookB); // paste all of the values you collected into the paste range you specified in book A }}References: None. This is mostly vanilla javascript taking advantage of the Apps Script Spreadsheet Class that you are already using in the sample in your question. 这篇关于在Google表格中将SetValues()设置为两个不同的单元格区域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！