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

huangapple go评论62阅读模式
英文:

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(&quot;1VlLbwBtYyOQBRz-8JHRn_jVhaAC7Tl68OXSltoQwaBU&quot;); //BLOCKS UPDATE 
  var ss2 = SpreadsheetApp.openById(&quot;10tb0zE_8i849T-hL6mU-Pw_4V-aMzNZJGNOFYG1F3qk&quot;); //Quotation
  var ss3 = SpreadsheetApp.openById(&quot;1pt7YnN9fmoD4PE0o9oVPezK8Qz6ZmabyJbWXfdzFeMU&quot;); //PRODUCTS
 
 //Extract data from User Update
  var lr1 = ss1.getSheetByName(&quot;BLOCK ORDERS&quot;).getLastRow();
  var data1 = ss1.getSheetByName(&quot;BLOCK ORDERS&quot;).getRange(2, 6, lr1, 61).getValues();

  //Extract data from Quotation Spreadsheet
  var sheetNames2 = [];
  ss2.getSheets().forEach(x =&gt; sheetNames2.push(x.getName()));

  //Process data 1
  var output = [];
  data1.forEach((x,i) =&gt; {
    var zoneSurcharge = x[58];
    
    if (sheetNames2.includes(x[0]) &amp;&amp; ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y =&gt; y.includes(x[10])).length &gt; 0 ) { 
      if (x[9] == &quot;EURO&quot; &amp;&amp; x[10] != &quot;&quot;) {
        output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][3]+zoneSurcharge]);
      }
      else if (x[9] == &quot;STERLING&quot; &amp;&amp; x[10] != &quot;&quot;) {
        output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][2]+zoneSurcharge]);
      }
    }
    else  
      if (x[9] == &quot;EURO&quot;  &amp;&amp; x[10] != &quot;&quot; ) {surcharges = zoneSurcharge 
        output.push([ss3.getSheetByName(&quot;PRODUCTS&quot;).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][3]+zoneSurcharge]) ;
      }
      else 
      if (x[9] == &quot;STERLING&quot;  &amp;&amp; x[10] != &quot;&quot;) {surcharges = zoneSurcharge 
        output.push([ss3.getSheetByName(&quot;PRODUCTS&quot;).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][2]+zoneSurcharge]); 
      }   
  });
  console.log(output);
 //Output 
 ss1.getSheetByName(&quot;BLOCK ORDERS&quot;).getRange(2, 26, output.length, output[0].length).setValues(output); 
   }

The output range ss1.getSheetByName(&quot;BLOCK ORDERS&quot;).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

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

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

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

答案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) =&gt; {
    var zoneSurcharge = x[58];
    if (x[10] == &quot;&quot;) {
      output.push([&quot;&quot;])
    } else {
      if (sheetNames2.includes(x[0]) &amp;&amp; ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y =&gt; y.includes(x[10])).length &gt; 0) {
        if (x[9] == &quot;EURO&quot; &amp;&amp; x[10] != &quot;&quot;) {
          output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][3] + zoneSurcharge]);
        }
        else if (x[9] == &quot;STERLING&quot; &amp;&amp; x[10] != &quot;&quot;) {
          output.push([ss2.getSheetByName(x[0]).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][2] + zoneSurcharge]);
        }
      }
      else
        if (x[9] == &quot;EURO&quot; &amp;&amp; x[10] != &quot;&quot;) {
          surcharges = zoneSurcharge
          output.push([ss3.getSheetByName(&quot;PRODUCTS&quot;).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][3] + zoneSurcharge]);
        }
        else
          if (x[9] == &quot;STERLING&quot; &amp;&amp; x[10] != &quot;&quot;) {
            surcharges = zoneSurcharge
            output.push([ss3.getSheetByName(&quot;PRODUCTS&quot;).getDataRange().getValues().filter(y =&gt; y.includes(x[10]))[0][2] + zoneSurcharge]);
          }
    }
  });

As you can see, I just encased everything in another if. So for a product input like [&quot;B94&quot;, &quot;&quot;, &quot;B94&quot;, &quot;C3520&quot;] your output will look like [[ &#39;77&#39; ],[ &#39;&#39; ],[ 71 ],[ &#39;103&#39; ]]. 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.

huangapple
  • 本文由 发表于 2023年2月18日 22:42:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/75494083.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定