英文:
Set value into merged cells from regular cell
问题
我尝试将普通单元格的值设置到合并单元格中。目前，当我尝试将值设置到合并单元格中时，它只设置第一个值，而忽略其余的值。
function generate(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s1 = ss.getSheetByName('Newsletters');
var s2 = ss.getSheetByName('Sheet1');
var lastRow = s1.getLastRow()*4;
for (var i = 5; i <= lastRow; i++) { // 从第5行开始
var country_src = s1.getRange(i, 10).getValues(); //国家
var languages_src = s1.getRange(i, 9).getValues(); //语言
var country = s2.getRange(i, 1); //国家
var languages = s2.getRange(i, 2); //语言
if (languages_src != '') {
languages.setValue(languages_src);
country.setValue(country_src);
}
}
}
我想实现以下效果：
目前它给我这个效果：
英文:
I'm trying to set value from regular cell into merged cells. Currently when I'm trying to set values into merged values it only sets first value and ignores rest of them.
function generate(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s1 = ss.getSheetByName('Newsletters');
var s2 = ss.getSheetByName('Sheet1');
var lastRow = s1.getLastRow()*4;
for (var i = 5; i <= lastRow; i++) { // Start from row 5
var country_src = s1.getRange([i], 10).getValues(); //Country
var languages_src = s1.getRange([i], 9).getValues(); //Languages
var country = s2.getRange([i], 1); //Country
var languages = s2.getRange([i], 2); //Languages
if (languages_src != '') {
languages.setValue(languages_src);
country.setValue(country_src);
}
}
}
I want to go from this:
To this:
Currently it gives me this:
答案1
得分: 2
这是一个生成你所需结果的示例。我创建了一个类似于你的源数据的电子表格。
然后，我创建了一个脚本来处理源数据并生成结果。使用这个脚本，我获取了每个单元格的values和colors。然后，我创建了一个填充好的results数组。请注意，对于源数据的每一行，需要生成4行结果数据。
Code.gs
function createMergedCells() {
try {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spread.getSheetByName("Sheet2");
let range = sheet.getDataRange(); // 没有标题
let values = range.getValues();
let colors = range.getFontColors();
sheet = spread.getSheetByName("Sheet3");
let i = 1;
let results = [];
values.forEach( (row,index) => {
if( row[0] !== "" ) {
range = sheet.getRange(i,1,4,1);
range.mergeVertically();
range.setFontColor(colors[index][0]);
range = sheet.getRange(i,2,4,1);
range.mergeVertically();
range.setFontColor(colors[index][1]);
i = i + 4;
results.push([row[0],row[1]]);
results.push(["",""]);
results.push(["",""]);
results.push(["",""]);
}
}
);
range = sheet.getRange(1,1,results.length,2);
range.setValues(results);
range.setVerticalAlignment("middle");
range.setHorizontalAlignment("center");
}
catch(err) {
console.log(err);
}
}
英文:
Here is an example of producing the results you want. I created a spreadsheet similar to your source.
Then I create a script to take the source and produce the results. With this script I get the values and colors of each cell. Then I create a results array filled in. Note that for each row of source there has to be 4 rows or results.
Code.gs
function createMergedCells() {
try {
let spread = SpreadsheetApp.getActiveSpreadsheet();
let sheet = spread.getSheetByName("Sheet2");
let range = sheet.getDataRange(); // no headers
let values = range.getValues();
let colors = range.getFontColors();
sheet = spread.getSheetByName("Sheet3");
let i = 1;
let results = [];
values.forEach( (row,index) => {
if( row[0] !== "" ) {
range = sheet.getRange(i,1,4,1)
range.mergeVertically();
range.setFontColor(colors[index][0]);
range = sheet.getRange(i,2,4,1)
range.mergeVertically();
range.setFontColor(colors[index][1]);
i = i + 4;
results.push([row[0],row[1]]);
results.push(["",""]);
results.push(["",""]);
results.push(["",""]);
}
}
);
range = sheet.getRange(1,1,results.length,2);
range.setValues(results);
range.setVerticalAlignment("middle");
range.setHorizontalAlignment("center");
}
catch(err) {
console.log(err);
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论