Skip to content

Commit 3fb6883

Browse files
grantgrant
committed
Add Google Sheets snippets
1 parent 231d5b0 commit 3fb6883

File tree

9 files changed

+796
-0
lines changed

9 files changed

+796
-0
lines changed

‎sheets/snippets/.gitignore‎

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
# Covers JetBrains IDEs: IntelliJ, RubyMine, PhpStorm, AppCode, PyCharm, CLion, Android Studio
2+
3+
*.iml
4+
5+
## Directory-based project format:
6+
.idea/
7+
8+
# if you remove the above rule, at least ignore the following:
9+
10+
# User-specific stuff:
11+
# .idea/workspace.xml
12+
# .idea/tasks.xml
13+
# .idea/dictionaries
14+
# .idea/shelf
15+
16+
# Sensitive or high-churn files:
17+
# .idea/dataSources.ids
18+
# .idea/dataSources.xml
19+
# .idea/sqlDataSources.xml
20+
# .idea/dynamic.xml
21+
# .idea/uiDesigner.xml
22+
23+
# Gradle:
24+
.idea/gradle.xml
25+
.idea/libraries
26+
27+
# Mongo Explorer plugin:
28+
# .idea/mongoSettings.xml
29+
30+
## File-based project format:
31+
*.ipr
32+
*.iws
33+
34+
## Plugin-specific files:
35+
36+
# IntelliJ
37+
/out/
38+
39+
# mpeltonen/sbt-idea plugin
40+
.idea_modules/
41+
42+
# JIRA plugin
43+
atlassian-ide-plugin.xml
44+
45+
# Crashlytics plugin (for Android Studio and IntelliJ)
46+
com_crashlytics_export_strings.xml
47+
crashlytics.properties
48+
crashlytics-build.properties
49+
fabric.properties
50+
51+
.gradle
52+
build/
53+
bin/
54+
55+
# Ignore Gradle GUI config
56+
gradle-app.setting
57+
58+
# Avoid ignoring Gradle wrapper jar file (.jar files are usually ignored)
59+
!gradle-wrapper.jar
60+
61+
# Cache of project
62+
.gradletasknamecache
63+
64+
# Eclipse
65+
.project
66+
.settings/
67+
.classpath

‎sheets/snippets/build.gradle‎

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
apply plugin: 'java'
2+
3+
repositories {
4+
// Use 'jcenter' for resolving your dependencies.
5+
jcenter()
6+
}
7+
8+
dependencies {
9+
compile 'com.google.api-client:google-api-client:1.22.0'
10+
compile 'com.google.apis:google-api-services-drive:v3-rev45-1.22.0'
11+
compile 'com.google.apis:google-api-services-sheets:v4-rev30-1.22.0'
12+
testCompile 'junit:junit:4.12'
13+
}
14+
15+
test {
16+
testLogging.showStandardStreams = true
17+
}

‎sheets/snippets/gradle/wrapper/gradle-wrapper.jar‎

52.4 KB
Binary file not shown.

‎sheets/snippets/settings.gradle‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,19 @@
1+
/*
2+
* This settings file was auto generated by the Gradle buildInit task
3+
* by 'sbazyl' at '10/22/15 11:30 AM' with Gradle 2.7
4+
*
5+
* The settings file is used to specify which projects to include in your build.
6+
* In a single project build this file can be empty or even removed.
7+
*
8+
* Detailed information about configuring a multi-project build in Gradle can be found
9+
* in the user guide at https://docs.gradle.org/2.7/userguide/multi_project_builds.html
10+
*/
11+
12+
/*
13+
// To declare projects as part of a multi-project build use the 'include' method
14+
include 'shared'
15+
include 'api'
16+
include 'services:webservice'
17+
*/
18+
19+
rootProject.name = 'java'

‎sheets/snippets/src/main/java/SpreadsheetPivotTables.java‎

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,117 @@
1+
import com.google.api.services.sheets.v4.Sheets;
2+
import com.google.api.services.sheets.v4.model.*;
3+
4+
import java.io.IOException;
5+
6+
import java.util.Arrays;
7+
import java.util.Collections;
8+
import java.util.List;
9+
10+
public class SpreadsheetPivotTables {
11+
private static final int SHEET_ID = 1337;
12+
private Sheets service;
13+
14+
public BatchUpdateSpreadsheetResponse pivotTable(String spreadsheetId, List<String> ranges) throws IOException {
15+
Sheets service = this.service;
16+
int targetSheetId = 123;
17+
18+
Request request = new Request().setUpdateCells(new UpdateCellsRequest()
19+
.setRows(Collections.singletonList(new RowData().setValues(Collections.singletonList(
20+
new CellData().setPivotTable(
21+
new PivotTable().setSource(
22+
new GridRange()
23+
.setSheetId(SHEET_ID)
24+
.setStartRowIndex(0)
25+
.setEndColumnIndex(0)
26+
.setEndRowIndex(101)
27+
.setEndColumnIndex(8)
28+
).setRows(Collections.singletonList(
29+
new PivotGroup()
30+
.setSourceColumnOffset(6)
31+
.setShowTotals(true)
32+
.setSortOrder("ASCENDING")
33+
)).setColumns(Collections.singletonList(
34+
new PivotGroup()
35+
.setSourceColumnOffset(3)
36+
.setSortOrder("ASCENDING")
37+
.setShowTotals(true)
38+
)).setValues(Collections.singletonList(
39+
new PivotValue()
40+
.setSummarizeFunction("COUNTA")
41+
.setSourceColumnOffset(3)
42+
)).setValueLayout("HORIZONTAL"))
43+
))
44+
))
45+
.setStart(new GridCoordinate()
46+
.setSheetId(targetSheetId)
47+
.setRowIndex(0)
48+
.setColumnIndex(0)
49+
)
50+
.setFields("pivotTable")
51+
);
52+
53+
BatchUpdateSpreadsheetRequest body =
54+
new BatchUpdateSpreadsheetRequest().setRequests(Collections.singletonList(request));
55+
BatchUpdateSpreadsheetResponse result = service.spreadsheets()
56+
.batchUpdate(spreadsheetId, body)
57+
.execute();
58+
59+
// [END batchGetValues]
60+
return result;
61+
}
62+
63+
public BatchUpdateSpreadsheetResponse conditionalFormatting(String spreadsheetId) throws IOException {
64+
List<GridRange> ranges = Collections.singletonList(new GridRange()
65+
.setSheetId(0)
66+
.setStartRowIndex(1)
67+
.setEndRowIndex(11)
68+
.setStartColumnIndex(0)
69+
.setEndColumnIndex(4)
70+
);
71+
List<Request> requests = Arrays.asList(
72+
new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest()
73+
.setRule(new ConditionalFormatRule()
74+
.setRanges(ranges)
75+
.setBooleanRule(new BooleanRule()
76+
.setCondition(new BooleanCondition()
77+
.setType("CUSTOM_FORMULA")
78+
.setValues(Collections.singletonList(
79+
new ConditionValue()
80+
.setUserEnteredValue("=GT($D2,median($D$2:$D$11))")
81+
))
82+
)
83+
.setFormat(new CellFormat().setTextFormat(
84+
new TextFormat().setForegroundColor(new Color().setRed(0.8f))
85+
))
86+
)
87+
)
88+
.setIndex(0)
89+
),
90+
new Request().setAddConditionalFormatRule(new AddConditionalFormatRuleRequest()
91+
.setRule(new ConditionalFormatRule()
92+
.setRanges(ranges)
93+
.setBooleanRule(new BooleanRule()
94+
.setCondition(new BooleanCondition()
95+
.setType("CUSTOM_FORMULA")
96+
.setValues(Collections.singletonList(
97+
new ConditionValue()
98+
.setUserEnteredValue("=LT($D2,median($D$2:$D$11))")
99+
))
100+
)
101+
.setFormat(new CellFormat().setBackgroundColor(
102+
new Color().setRed(1f).setGreen(0.4f).setBlue(0.4f)
103+
))
104+
)
105+
)
106+
.setIndex(0)
107+
)
108+
);
109+
110+
BatchUpdateSpreadsheetRequest body = new BatchUpdateSpreadsheetRequest().setRequests(requests);
111+
BatchUpdateSpreadsheetResponse result = service.spreadsheets()
112+
.batchUpdate(spreadsheetId, body)
113+
.execute();
114+
115+
return result;
116+
}
117+
}

0 commit comments

Comments
 (0)