英文:

How to use year counter google app script?

问题

Sure, here's the translated content:

我在Google表格中有以下数据：

我需要使用Google Apps脚本将年份计数添加到列D，如下所示：

英文:

i have following data in a google sheet

i need to add a year counter to the column d as follows using google apps script

答案1

得分: 0

注意：此脚本旨在作为您项目的起点或参考使用。请注意，社区成员不提供编码服务。

根据您的描述，似乎您想要在列D中每个单元格中减去后续年份，年份之间用换行符（\n）分隔。减法的结果应显示在相应的A列引用旁边，也用换行符分隔。

要在Google Apps Script中解决这个问题，您可以使用换行符将列D的值拆分，然后减去后续年份，最后用换行符连接结果。以下是一个示例脚本作为自定义Google表格函数：

示例脚本 [已更新]

function yearsCount(range) {
  var letters = [];
  var years = [];
  range.forEach(x => (letters.push(x[0].split(/\r?\n/)), years.push(x[1].split(/\r?\n/)))); //Loop & push the letters & years

  return years.map(x => x.map((y, i, arr) => { //Iterate through the years
    try {
      var yearData = (parseInt(arr[i + 1].match(/\d{4}/)[0]) - parseInt(y.match(/\d{4}/)[0]));
      return letters[0][i] + ' ' + (yearData > 1 ? yearData + ' years' : yearData + ' year');//Subtract the current year to the next year
    } catch {
      var yearData2 = new Date().getUTCFullYear() - parseInt(y.match(/\d{4}/)[0]); //Subract the last year to the current year
      return letters[0][i] + ' ' + (yearData2 > 1 ? yearData2 + ' years' : yearData2 + ' year');
    }
  })).map(x => [x.join('\n')]); // restructure the year array and join them with new line to be returned back to sheet
}

演示

参考资料

• map()

• Google Sheets中的自定义函数

<details>
<summary>英文:</summary>

Suggestion
==========

&gt; ***NOTE**: This script is intended to serve as a starting point or reference for your project. It&#39;s important to note that the community members do not provide coding services.*

Based on your description, it seems you want to subtract the subsequent years per cell in column D, where the years are separated by `new lines (\n)`. The result of the subtraction should be displayed adjacent to the corresponding column A references, also separated by new lines.

To tackle this in Google Apps Script, you can split the column D values by the new line character, subtract the subsequent years, and then join the results with new lines. Here&#39;s an example script as a custom Google Sheet function:

Sample Script *[updated]*
-------------

    function yearsCount(range) {
      var letters = [];
      var years = [];
      range.forEach(x =&gt; (letters.push(x[0].split(/\r?\n/)), years.push(x[1].split(/\r?\n/)))); //Loop &amp; push the letters &amp; years
    
      return years.map(x =&gt; x.map((y, i, arr) =&gt; { //Iterate through the years
        try {
          var yearData = (parseInt(arr[i + 1].match(/\d{4}/)[0]) - parseInt(y.match(/\d{4}/)[0]));
          return letters[0][i] + &#39; &#39; + (yearData &gt; 1 ? yearData + &#39; years&#39; : yearData + &#39; year&#39;);//Subtract the current year to the next year
        } catch {
          var yearData2 = new Date().getUTCFullYear() - parseInt(y.match(/\d{4}/)[0]); //Subract the last year to the current year
          return letters[0][i] + &#39; &#39; + (yearData2 &gt; 1 ? yearData2 + &#39; years&#39; : yearData2 + &#39; year&#39;);
        }
      })).map(x =&gt; [x.join(&#39;\n&#39;)]); // restructure the year array and join them with new line to be returned back to sheet
    }

Demo
----

[![enter image description here][1]][1]

References
----------

- [map()][2]
- [Custom Functions in Google Sheets][3]


  [1]: https://i.stack.imgur.com/o5uPe.gif
  [2]: https://spreadsheet.dev/array-method-map-in-apps-script
  [3]: https://developers.google.com/apps-script/guides/sheets/functions

</details>