FilesExpand file tree

 main

/

019_IntroToAppsScript_Charts.js

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

133 lines (90 loc) · 3.1 KB

 main

/

019_IntroToAppsScript_Charts.js

Copy path

Top

File metadata and controls

• Code
• Blame

133 lines (90 loc) · 3.1 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

// add total for each row into our Sheet

function addRowTotals() {

const ss = SpreadsheetApp.getActive();

const sheet1 = ss.getSheetByName('Sheet1');

const lastRow = sheet1.getLastRow();

const data = sheet1.getRange(2,1,lastRow - 1,3).getValues();

console.log(data);

const totalsArray = [];

data.forEach(function(row) {

const name = row[0];

const cost = row[1];

const quantity = row[2];

const total = cost * quantity;

row.push(total);

totalsArray.push([total]);

console.log(row);

});

console.log(totalsArray);

// paste data back into Sheet

sheet1.getRange(2,4,lastRow - 1,1).setValues(totalsArray);

}

// add menu

// onOpen is a special function

// runs when your Sheet opens

function onOpen() {

const ui = SpreadsheetApp.getUi();

ui.createMenu('Custom Menu')

.addItem('Add Row Totals','addRowTotals')

.addItem('Add Grand Total','grandTotal')

.addItem('Format Table','formatTable')

.addItem('Add Chart','addChart')

.addToUi();

}

// add grand total function

function grandTotal() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet1 = ss.getSheetByName('Sheet1');

const lastRow = sheet1.getLastRow();

const lastColumn = sheet1.getLastColumn();

// add total row

sheet1.getRange(lastRow + 1,1).setValue('TOTAL');

sheet1.getRange(lastRow + 1, lastColumn).setFormula('=SUM(D2:D' + lastRow + ')');

}

// format the table

function formatTable() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet1 = ss.getSheetByName('Sheet1');

const lastRow = sheet1.getLastRow();

const lastColumn = sheet1.getLastColumn();

const dataRange = sheet1.getDataRange();

// orange row banding

dataRange.applyRowBanding(SpreadsheetApp.BandingTheme.ORANGE,true,true);

// add dollar signs to totals

sheet1.getRange(2,lastColumn,lastRow - 1,1).setNumberFormat('$#,##0.00');

// make the grand total row bold

sheet1.getRange(lastRow,1,1,lastColumn).setFontWeight('bold');

}

// add a chart

function addChart() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet1 = ss.getSheetByName('Sheet1');

const lastRow = sheet1.getLastRow();

const lastColumn = sheet1.getLastColumn();

// get data labels and values

const totalChartLabels = sheet1.getRange(1,1,lastRow - 1,1);

const totalChartValues = sheet1.getRange(1,lastColumn,lastRow - 1,1);

// create the chart

const totalsChart = sheet1.newChart()

.setChartType(Charts.ChartType.BAR)

.addRange(totalChartLabels)

.addRange(totalChartValues)

.setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)

.setPosition(1,5,0,0)

.setNumHeaders(1)

.setOption('title','Fruit Chart')

.build();

// insert Chart into Sheet

sheet1.insertChart(totalsChart);

/*

const dataRange = sheet1.getRange('A1:B9');

console.log(dataRange.getValues());

const myNewChart = sheet1.newChart();

myNewChart.addRange(dataRange)

.setChartType(Charts.ChartType.BAR)

.setPosition(1,5,0,0)

.setNumHeaders(1)

.setOption('title','Fruit Chart');

sheet1.insertChart(myNewChart.build());

*/

}