英文:
How to setvalues() in the row of a column which is in the same row as a cell in another column with google apps script
问题
这是您提供的脚本,它用于从“BLOCK ORDERS”工作表的P列提取产品代码,然后根据匹配特定客户的工作表名称从“QUOTATION”工作表或从“PRODUCTS”工作表提取价格,最后将价格输入到“BLOCK ORDERS”工作表的Z列。您的问题是如何确保价格放入与产品代码相同的行,因为产品代码可能出现在不同的行。以下是您的要求部分的翻译:
//Output
ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, output.length, output[0].length).setValues(output);
上述代码中,您从第2行开始设置输出范围的第一行(26列),但您希望它与产品代码所在的行对应。为了实现这一点,您可以根据产品代码的位置调整输出范围的起始行。下面是如何根据产品代码的位置进行调整的修改:
// Find the column index where the product code is located (assuming it's in column P)
var productCodeColumnIndex = 16; // Column P is the 16th column (0-based index)
// Output based on the row where the product code is found
var outputRangeStartRow = 2; // Start from row 2
for (var i = 0; i < output.length; i++) {
var productCodeRowIndex = i + 2; // Assuming the product code is in column P, and we start from row 2
ss1.getSheetByName("BLOCK ORDERS").getRange(productCodeRowIndex, productCodeColumnIndex + 10, 1, output[0].length).setValues([output[i]]);
}
此修改将根据产品代码的位置自动调整输出的行,以确保价格放入与产品代码相同的行。
英文:
I have the following script which works great, for a product code in column P of the BLOCK ORDERS sheet, ss1, it extracts the price from either the quotation worksheets ss2 if a sheet name matches the name of a particular customer, or if not then the price is taken from products sheet ss3. The price is input into column Z of the BLOCK ORDERS sheet. The problem I have is how to get the script to put the price in the same row as that of the product code?
function updateQuotationPrice() {
var ss1 = SpreadsheetApp.openById("1VlLbwBtYyOQBRz-8JHRn_jVhaAC7Tl68OXSltoQwaBU"); //BLOCKS UPDATE
var ss2 = SpreadsheetApp.openById("10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk"); //Quotation
var ss3 = SpreadsheetApp.openById("1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU"); //PRODUCTS
//Extract data from User Update
var lr1 = ss1.getSheetByName("BLOCK ORDERS").getLastRow();
var data1 = ss1.getSheetByName("BLOCK ORDERS").getRange(2, 6, lr1, 61).getValues();
//Extract data from Quotation Spreadsheet
var sheetNames2 = [];
ss2.getSheets().forEach(x => sheetNames2.push(x.getName()));
//Process data 1
var output = [];
data1.forEach((x,i) => {
var zoneSurcharge = x[58];
if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10])).length > 0 ) {
if (x[9] == "EURO" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10]))[0][3]+zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10]))[0][2]+zoneSurcharge]);
}
}
else
if (x[9] == "EURO" && x[10] != "" ) {surcharges = zoneSurcharge
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][3]+zoneSurcharge]) ;
}
else
if (x[9] == "STERLING" && x[10] != "") {surcharges = zoneSurcharge
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][2]+zoneSurcharge]);
}
});
console.log(output);
//Output
ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, output.length, output[0].length).setValues(output);
}
The output range ss1.getSheetByName("BLOCK ORDERS").getRange(2, 26, output.length, output[0].length).setValues(output);
starts at row 2. I require the output to correspond with the same row which contains the product code. Sometimes the product code could be in row 2 or row 3 ,or row 4 etc.
The BLOCK ORDERS sheets can be viewed at
https://docs.google.com/spreadsheets/d/1VlLbwBtYyOQBRz-8JHRn_jVhaAC7Tl68OXSltoQwaBU/edit?usp=sharing
The QUOTATIONS sheets can be viewed at
https://docs.google.com/spreadsheets/d/10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk/edit?usp=sharing
The PRODUCTS sheets can be viewed at
https://docs.google.com/spreadsheets/d/1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU/edit?usp=sharing
Thank you for any help with this
答案1
得分: 1
I don't know if there are any edge cases that you need to take into account, but the way your current script works, the output
prices will always be in the same order as your products
in column P. So all you need to do is to fill the output
array with blank strings when you also find a blank product. So your loop would look like this:
var output = [];
data1.forEach((x, i) => {
var zoneSurcharge = x[58];
if (x[10] == "") {
output.push([""])
} else {
if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10])).length > 0) {
if (x[9] == "EURO" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y includes(x[10]))[0][3] + zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y includes(x[10]))[0][2] + zoneSurcharge]);
}
}
else
if (x[9] == "EURO" && x[10] != "") {
surcharges = zoneSurcharge
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y includes(x[10]))[0][3] + zoneSurcharge]);
}
else
if (x[9] == "STERLING" && x[10] != "") {
surcharges = zoneSurcharge
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y includes(x[10]))[0][2] + zoneSurcharge]);
}
}
});
As you can see, I just encased everything in another if
. So for a product input like ["B94", "", "B94", "C3520"]
your output
will look like [[ '77' ],[ '' ],[ 71 ],[ '103' ]]
. The products will match with their prices. You can also add to the condition any other columns that you expect could be blank like currency
, or to validate for missing product codes, but this will depend on how your sheet works. The general idea is that you just want to fill the output
with blank rows whenever you cannot retrieve a price so it will match the input.
The hard way would probably be something like keeping track of the index
rows in the forEach
to create a RangeList
with only the valid rows and then set their values. This seems overkill for your use case, though.
英文:
I don't know if there are any edge cases that you need to take into account, but the way your current script works, the output
prices will always be in the same order as your products
in column P. So all you need to do is to fill the output
array with blank strings when you also find a blank product. So your loop would look like this:
var output = [];
data1.forEach((x, i) => {
var zoneSurcharge = x[58];
if (x[10] == "") {
output.push([""])
} else {
if (sheetNames2.includes(x[0]) && ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10])).length > 0) {
if (x[9] == "EURO" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10]))[0][3] + zoneSurcharge]);
}
else if (x[9] == "STERLING" && x[10] != "") {
output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y => y.includes(x[10]))[0][2] + zoneSurcharge]);
}
}
else
if (x[9] == "EURO" && x[10] != "") {
surcharges = zoneSurcharge
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][3] + zoneSurcharge]);
}
else
if (x[9] == "STERLING" && x[10] != "") {
surcharges = zoneSurcharge
output.push([ss3.getSheetByName("PRODUCTS").getDataRange().getValues().filter(y => y.includes(x[10]))[0][2] + zoneSurcharge]);
}
}
});
As you can see, I just encased everything in another if
. So for a product input like ["B94", "", "B94", "C3520"]
your output
will look like [[ '77' ],[ '' ],[ 71 ],[ '103' ]]
. The products will match with their prices. You can also add to the condition any other columns that you expect could be blank like currency
, or to validate for missing product codes, but this will depend on how your sheet works. The general idea is that you just want to fill the output
with blank rows whenever you cannot retrieve a price so it will match the input.
The hard way would probably be something like keeping track of the index
rows in the forEach
to create a RangeList
with only the valid rows and then set their values. This seems overkill for your use case, though.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论