【Googleスプレッドシート、GAS】選択した範囲をHTMLのtableタグとしてテキスト出力する

1
/**
2
 * @param {string[]} values
3
 * @param {string} textReplacingIfBlank
4
 * @return {string}
5
*/
6
function getHtmlByValues(values, textReplacingIfBlank="💩"){
7
  let prefix = "<table>";
8
  let suffix = "</table>";
9
  let html = `${prefix}\n`;
10
  let tmp_tr = "";
11
  let isTh = false;
12
  for(let i = 0; i < values.length; i++){
13
    isTh = false;
14
    if(i === 0){
15
      isTh = true;
16
    }
17
    tmp_tr = getTrByRow(values[i], isTh, textReplacingIfBlank);
18
    html += `${tmp_tr}\n`;
19
  }
20
  html += suffix;
21
  return html;
22
}
23

24
/**
25
 * @param {string[]} row
26
 * @param {boolean} isTh
27
 * @param {string} textReplacingIfBlank
28
 * @return {string}
29
*/
30
function getTrByRow(row, isTh, textReplacingIfBlank="💩"){
31
  let element;
32
  let tr = "";
33
  let tagTd = "td";
34
  if(isTh){
35
    tagTd = "th";
36
    textReplacingIfBlank = "";
37
  }
38
  const tagTr = "tr";
39
  for(let i = 0; i< row.length; i++){
40
    element = closeByTag(tagTd, row[i], textReplacingIfBlank);
41
    tr += element;
42
  }
43
  tr = closeByTag(tagTr, tr, "");
44
  return tr;
45
}
46

47
/**
48
 * @param {string} tag without < and >
49
 * @param {string} innerText
50
 * @param {string} textReplacingIfBlank
51
 * @return {string}
52
*/
53
function closeByTag(tag, innerText, textReplacingIfBlank="💩"){
54
  if(tag[0] === "<"){
55
    throw Error("Initial of tag mustn't be \"<\".");
56
  }
57
  if(tag[tag.length - 1] === ">"){
58
    throw Error("End of tag mustn't be \">\".");
59
  }
60

61
  let initialOfTag = "<";
62
  let endOfTag = ">";
63
  let formerTag = `${initialOfTag}${tag}${endOfTag}`;
64
  let latterTag = `${initialOfTag}/${tag}${endOfTag}`;
65
  let displayText = innerText;
66
  if(displayText === ""){
67
    displayText = textReplacingIfBlank;
68
  }
69
  let element = `${formerTag}${displayText}${latterTag}`;
70
  return element;
71
}
72

73
function getValuesBySelectedArea(){
74
  const ss = SpreadsheetApp.getActive().getSheetByName(sheetName1st);
75
  const activeValues = ss.getActiveRange().getValues();
76
  console.log(activeValues);
77
  return activeValues;
78
}
79

80
function main(){
81
  const values = getValuesBySelectedArea();
82
  const html = getHtmlByValues(values, "");
83
  console.log(html);
84
}

1
// let tester = new TestGAS.TestGasExecutor();
2
let tester = TestGAS.createExecutor();
3

4
class Test_main{
5
  test_getHtmlByValues_1_1(){
6
    const values = [ [ '', 'Python', 'GAS', 'GitHub', 'JavaScript' ]
7
                    , [ '', '', '', '', '' ]
8
                  ];
9
    const textReplacingIfBlank = "";
10
    const actual = getHtmlByValues(values, textReplacingIfBlank);
11
    const expected = `<table>
12
<tr><th></th><th>Python</th><th>GAS</th><th>GitHub</th><th>JavaScript</th></tr>
13
<tr><td></td><td></td><td></td><td></td><td></td></tr>
14
</table>`;
15
    tester.assertEquals(actual, expected);
16
  }
17

18
  test_getHtmlByValues_1_2(){
19
    const values = [ [ '', 'Python', 'GAS', 'GitHub', 'JavaScript' ] ];
20
    const textReplacingIfBlank = "";
21
    const actual = getHtmlByValues(values, textReplacingIfBlank);
22
    const expected = `<table>
23
<tr><th></th><th>Python</th><th>GAS</th><th>GitHub</th><th>JavaScript</th></tr>
24
</table>`;
25
    tester.assertEquals(actual, expected);
26
  }
27

28
  test_getHtmlByValues_1_3(){
29
    const values = [ [ '' ] ];
30
    const textReplacingIfBlank = "";
31
    const actual = getHtmlByValues(values, textReplacingIfBlank);
32
    const expected = `<table>
33
<tr><th></th></tr>
34
</table>`;
35
    tester.assertEquals(actual, expected);
36
  }
37

38
  test_getHtmlByValues_2_1(){
39
    const values = [ [ '', 'Python', 'GAS', 'GitHub', 'JavaScript' ]
40
                    , [ '', '', '', '', '' ]
41
                  ];
42
    const actual = getHtmlByValues(values);
43
    const expected = `<table>
44
<tr><th></th><th>Python</th><th>GAS</th><th>GitHub</th><th>JavaScript</th></tr>
45
<tr><td>💩</td><td>💩</td><td>💩</td><td>💩</td><td>💩</td></tr>
46
</table>`;
47
    tester.assertEquals(actual, expected);
48
  }
49

50
  test_getTrByRow_1_1(){
51
    const row = [1, 2, 3];
52
    const isTh = false;
53
    const textReplacingIfBlank = "";
54
    const actual = getTrByRow(row, isTh, textReplacingIfBlank);
55
    const expected = "<tr><td>1</td><td>2</td><td>3</td></tr>";
56
    tester.assertEquals(actual, expected);
57
  }
58

59
  test_getTrByRow_1_2(){
60
    const row = [1, 2, 3];
61
    const isTh = true;
62
    const textReplacingIfBlank = "";
63
    const actual = getTrByRow(row, isTh, textReplacingIfBlank);
64
    const expected = "<tr><th>1</th><th>2</th><th>3</th></tr>";
65
    tester.assertEquals(actual, expected);
66
  }
67

68
  test_closeByTag_1_1(){
69
    const tag = "a";
70
    const innerText = "test";
71
    const textReplacingIfBlank = "";
72
    const actual = closeByTag(tag, innerText, textReplacingIfBlank);
73
    const expected = "<a>test</a>";
74
    tester.assertEquals(actual, expected);
75
  }
76

77
  test_closeByTag_1_2(){
78
    const tag = "a";
79
    const innerText = "test";
80
    const textReplacingIfBlank = "";
81
    const actual = closeByTag(tag, innerText, textReplacingIfBlank);
82
    const expected = "<a>test<//a>";
83
    tester.assertNotEquals(actual, expected);
84
  }
85

86
  test_closeByTag_2_1(){
87
    const tag = "<a href=";
88
    const innerText = "test";
89
    const textReplacingIfBlank = "";
90
    tester.assertError(closeByTag, [tag, innerText, textReplacingIfBlank], Error);
91
  }
92

93
  test_closeByTag_2_2(){
94
    const tag = "<a href=";
95
    const innerText = "test";
96
    tester.assertError(closeByTag, [tag, innerText], Error);
97
  }
98

99
  test_closeByTag_3_1(){
100
    const tag = "a href=\"\">";
101
    const innerText = "test";
102
    const textReplacingIfBlank = "";
103
    tester.assertError(closeByTag, [tag, innerText, textReplacingIfBlank], Error);
104
  }
105

106
  test_closeByTag_3_2(){
107
    const tag = "a href=\"\">";
108
    const innerText = "test";
109
    tester.assertError(closeByTag, [tag, innerText], Error);
110
  }
111

112
}
113

114
function execute_Test_main(){
115
  let failureFuncs = tester.executeTestGas(Test_main);
116
}