【GAS】AppSheetでグラフを描くために数値データを線形補間する

1
/**
2
 * @description Get linear-interpolated values by argued values.
3
 * @param {string[][]} values
4
 * @return {string[][]}
5
*/
6
function getLinearInterpolateByColumns(values){
7
  let baseIndices = [];
8
  let nodes = [];
9
  let numOfNodes = 0;
10
  let difference = 0;
11
  let remain = 0;
12
  for(let i = 0; i < values.length; i++){
13
    if(values[i][0] !== 0 && values[i][0] !== ""){
14
      baseIndices.push(i);
15
    }
16
  }
17
  console.log(`${baseIndices}`);
18
  console.log(`${baseIndices.length}`);
19
  console.log(`getLinearInterpolateByColumns: 111111111111111111111111111111111111111111111`);
20
  if(baseIndices.length < 2){
21
    console.log(`getLinearInterpolateByColumns: Not linear-interpolated.`);
22
    console.log(`getLinearInterpolateByColumns: 2222222222222222222222222222222222222222222`);
23
    return values;
24
  }
25
  for(let i = 0; i < baseIndices.length - 1; i++){
26
    if(i === 0){
27
      for(let j = 0; j < baseIndices[i]; j++){
28
        nodes.push(0);
29
      }
30
      console.log(nodes);
31
      console.log(`getLinearInterpolateByColumns: 33333333333333333333333333333333333333333333333`);
32
    }
33
    numOfNodes = baseIndices[i + 1] - baseIndices[i];
34
    difference = values[baseIndices[i + 1]][0] - values[baseIndices[i]][0];
35
    console.log(numOfNodes);
36
    console.log(difference);
37
    console.log(`getLinearInterpolateByColumns: 4444444444444444444444444444444444444444444444444`);
38
    for(let j = 0; j < numOfNodes; j++){
39
      console.log(values[baseIndices[i]][0]);
40
      console.log(difference * j / numOfNodes);
41
      nodes.push(values[baseIndices[i]][0] + difference * j / numOfNodes);
42
    }
43
    console.log(`getLinearInterpolateByColumns: 555555555555555555555555555555555555555555555555555`);
44
    if(i === baseIndices.length - 2){
45
      // remain = baseIndices[i + 1] - baseIndices[i];
46
      remain = values.length - 1 - baseIndices[i + 1];
47
      nodes.push(values[baseIndices[i + 1]][0]);
48
      for(let j = 0; j < remain; j++){
49
        nodes.push(0);
50
      }
51
    }
52
  }
53
  let roundedNodes = nodes.map(node => {
54
    return Math.round(node);
55
  })
56
  let newValues = roundedNodes.map(node => {
57
    return [node];
58
  })
59
  console.log(newValues);
60
  console.log(`getLinearInterpolateByColumns: 999999999999999999999999999999999999999999999999999999`);
61

62
  return newValues;
63
}
64

65
/**
66
 * @description Get the statement line-broken to display values in the argued arrays.
67
 * @param {string[][]} arrays
68
 * @return {string[][]}
69
*/
70
function getStatementLineBrokenToDisplayArrays(arrays, opening){
71
  const sepFirst = ":<br>";
72
  const sep = "<br>";
73
  let outNumberOfStart = arrays[0];
74
  const startOfStatement = `${opening}${sepFirst}${outNumberOfStart}${sep}`;
75
  let statement = arrays.reduce((prev, curr) => {
76
    return `${prev.toString()}${sep}${curr.toString()}`;
77
  });
78
  statement = statement.replace(`${outNumberOfStart}${sep}`, startOfStatement);
79
  return statement;
80
}
81

82
/**
83
 * @description Output linear-interpolated values by argued values.
84
 * @return {boolean}
85
*/
86
function outputLinearInterpolate(){
87
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
88
  let values = sheet.getActiveRange().getValues();
89
  console.log(values);
90
  console.log(`${values}`);
91
  console.log(`outputLinearInterpolate: 111111111111111111111111111111111111111111111`);
92

93
  if(values[0].length > 1){
94
    throw new RangeError("Length of selected column must be 1.")
95
  }
96

97
  let newValues = getLinearInterpolateByColumns(values);
98
  console.log(`${newValues}`);
99
  console.log(`outputLinearInterpolate: 222222222222222222222222222222222222222222222`);
100

101
  let statementBeforeValues = getStatementLineBrokenToDisplayArrays(values, "Before interpolation:");
102
  let statementAfterValues = getStatementLineBrokenToDisplayArrays(newValues, "After interpolation:");
103
  displayHtml("index_html", "Linear-interpolation Terminated...", `<p>${statementBeforeValues}</p><p>${statementAfterValues}</p>`);
104
  console.log(`outputLinearInterpolate: 999999999999999999999999999999999999999999999`);
105

106
  return true;
107
}