英文:
Adapt code so it outputs to the cell as a string
问题
我有以下的代码 - 它正在运行，但输出到控制台。我试图将其更改为将数组转换为文本字符串，然后将该字符串输出到电子表格。我尝试过 'return' 和其他方法，但我对这种脚本语言的了解有限。
function findInAllSheets(){
const text = "robert";
const ss = SpreadsheetApp.getActiveSpreadsheet();
const textFinder = ss.createTextFinder(text);
const allOccurrences = textFinder.findAll();
const locationList = allOccurrences.map(item => {
return {sheet:item.getSheet().getName(), cell: item.getA1Notation()};
});
console.log(locationList);
}
英文:
I have the below code - it's working but it outputs to the console. I'm trying to get it to change the array in to a text string and then output that string to the spreadsheet. I've tried 'return' and other methods but my knowledge of this scripting language is limited.
function findInAllSheets(){
const text = "robert"
const ss = SpreadsheetApp.getActiveSpreadsheet();
const textFinder = ss.createTextFinder(text)
const allOccurrences = textFinder.findAll();
const locationList = allOccurrences.map(item => {
return {sheet:item.getSheet().getName(), cell: item.getA1Notation()}
})
console.log(locationList)
}
答案1
得分: 1
由于您表示更喜欢将单元格作为输出，而不是填充行和列，那么可以尝试以下备选方法，使用自定义函数：
function findInAllSheets(){
const text = "robert";
const ss = SpreadsheetApp.getActiveSpreadsheet();
const textFinder = ss.createTextFinder(text);
const allOccurrences = textFinder.findAll();
const locationList = allOccurrences.map(item => {
return {sheet:item.getSheet().getName(), cell: item.getA1Notation()}
});
var a = locationList.map(object => object.sheet + ">" + object.cell).join();
return[[locationList.map(object => object.sheet + ">" + object.cell).join()]];
}
假设您希望无论文本在工作表中出现多少次，都要将其A1表示法输出到单个单元格。
结果:
更新后的结果:
英文:
Since you stated that you preferred a single cell to be the output rather than filling rows and columns, how about trying the following alternative approach using a custom function:
function findInAllSheets(){
const text = "robert"
const ss = SpreadsheetApp.getActiveSpreadsheet();
const textFinder = ss.createTextFinder(text)
const allOccurrences = textFinder.findAll();
const locationList = allOccurrences.map(item => {
return {sheet:item.getSheet().getName(), cell: item.getA1Notation()}
})
//console.log(locationList.map(object => object.cell))
//return[[locationList.map(object => object.cell).join()]] //returns the A1 notation of the specific text
//If you want the sheet name to be included as you have mentioned
var a = locationList.map(object => object.sheet + ">" + object.cell).join()
console.log(a)
return[[locationList.map(object => object.sheet + ">" + object.cell).join()]]
}
Assuming that you may want to output its A1 notation in a single cell regardless of the number of instances the text appears in a sheet.
result:
Updated Result:
答案2
得分: 0
function findInAllSheets(){
const text = "1";
const ss = SpreadsheetApp.getActive();
const tf = ss.createTextFinder(text);
const list = tf.findAll().map(rg => {
return [rg.getSheet().getName() + "/" + rg.getA1Notation()]
})
list.unshift(["name / Range"])
//Logger.log(JSON.stringify(list));
let sh = ss.getSheetByName("Sheet0");
sh.getRange(1,1,list.length,list[0].length).setValues(list)
}
References:
英文:
function findInAllSheets(){
const text = "1"
const ss = SpreadsheetApp.getActive();
const tf = ss.createTextFinder(text);
const list = tf.findAll().map(rg => {
return [rg.getSheet().getName() + "/" + rg.getA1Notation()]
})
list.unshift(["name / Range"])
//Logger.log(JSON.stringify(list));
let sh = ss.getSheetByName("Sheet0");
sh.getRange(1,1,list.length,list[0].length).setValues(list)
}
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论