Skip to content

Commit cb2bc16

Browse files
RajeshGovosanjuktaghosh7Manvendra-P-Singh
authored
Sheets snippets1 (googleworkspace#213)
* git-on-borg files of gmail-api-snippets * Update build.gradle test12 * Update build.gradle * Spreadsheet snippets Co-authored-by: sanjuktaghosh7 <sanjuktaghosh@google.com> Co-authored-by: Manvendra-P-Singh <singhmanvendra@google.com>
1 parent d3dc7de commit cb2bc16

6 files changed

Lines changed: 562 additions & 0 deletions

File tree

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

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,87 @@
1+
// Copyright 2022 Google LLC
2+
//
3+
// Licensed under the Apache License, Version 2.0 (the "License");
4+
// you may not use this file except in compliance with the License.
5+
// You may obtain a copy of the License at
6+
//
7+
// https://www.apache.org/licenses/LICENSE-2.0
8+
//
9+
// Unless required by applicable law or agreed to in writing, software
10+
// distributed under the License is distributed on an "AS IS" BASIS,
11+
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
// See the License for the specific language governing permissions and
13+
// limitations under the License.
14+
15+
16+
// [START sheets_append_values]
17+
import com.google.api.client.googleapis.json.GoogleJsonError;
18+
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
19+
import com.google.api.client.http.HttpRequestInitializer;
20+
import com.google.api.client.http.javanet.NetHttpTransport;
21+
import com.google.api.client.json.gson.GsonFactory;
22+
import com.google.api.services.sheets.v4.Sheets;
23+
import com.google.api.services.sheets.v4.SheetsScopes;
24+
import com.google.api.services.sheets.v4.model.AppendValuesResponse;
25+
import com.google.api.services.sheets.v4.model.ValueRange;
26+
import com.google.auth.http.HttpCredentialsAdapter;
27+
import com.google.auth.oauth2.GoogleCredentials;
28+
29+
import java.io.IOException;
30+
import java.util.Collections;
31+
import java.util.List;
32+
33+
/* Class to demonstrate the use of Spreadsheet Append Values API */
34+
public class AppendValues {
35+
/**
36+
* Appends values to a spreadsheet.
37+
*
38+
* @param spreadsheetId - Id of the spreadsheet.
39+
* @param range - Range of cells of the spreadsheet.
40+
* @param valueInputOption - Determines how input data should be interpreted.
41+
* @param values - list of rows of values to input.
42+
* @return spreadsheet with appended values
43+
* @throws IOException - if credentials file not found.
44+
*/
45+
public static AppendValuesResponse appendValues(String spreadsheetId,
46+
String range,
47+
String valueInputOption,
48+
List<List<Object>> values)
49+
throws IOException {
50+
/* Load pre-authorized user credentials from the environment.
51+
TODO(developer) - See https://developers.google.com/identity for
52+
guides on implementing OAuth2 for your application. */
53+
GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
54+
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
55+
HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
56+
credentials);
57+
58+
// Create the sheets API client
59+
Sheets service = new Sheets.Builder(new NetHttpTransport(),
60+
GsonFactory.getDefaultInstance(),
61+
requestInitializer)
62+
.setApplicationName("Sheets samples")
63+
.build();
64+
65+
AppendValuesResponse result = null;
66+
try {
67+
// Append values to the specified range.
68+
ValueRange body = new ValueRange()
69+
.setValues(values);
70+
result = service.spreadsheets().values().append(spreadsheetId, range, body)
71+
.setValueInputOption(valueInputOption)
72+
.execute();
73+
// Prints the spreadsheet with appended values.
74+
System.out.printf("%d cells appended.", result.getUpdates().getUpdatedCells());
75+
} catch (GoogleJsonResponseException e) {
76+
// TODO(developer) - handle error appropriately
77+
GoogleJsonError error = e.getDetails();
78+
if (error.getCode() == 404) {
79+
System.out.printf("Spreadsheet not found with id '%s'.\n",spreadsheetId);
80+
} else {
81+
throw e;
82+
}
83+
}
84+
return result;
85+
}
86+
}
87+
// [END sheets_append_values]

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

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,78 @@
1+
// Copyright 2022 Google LLC
2+
//
3+
// Licensed under the Apache License, Version 2.0 (the "License");
4+
// you may not use this file except in compliance with the License.
5+
// You may obtain a copy of the License at
6+
//
7+
// https://www.apache.org/licenses/LICENSE-2.0
8+
//
9+
// Unless required by applicable law or agreed to in writing, software
10+
// distributed under the License is distributed on an "AS IS" BASIS,
11+
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
// See the License for the specific language governing permissions and
13+
// limitations under the License.
14+
15+
16+
// [START sheets_batch_get_values]
17+
import com.google.api.client.googleapis.json.GoogleJsonError;
18+
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
19+
import com.google.api.client.http.HttpRequestInitializer;
20+
import com.google.api.client.http.javanet.NetHttpTransport;
21+
import com.google.api.client.json.gson.GsonFactory;
22+
import com.google.api.services.sheets.v4.Sheets;
23+
import com.google.api.services.sheets.v4.SheetsScopes;
24+
import com.google.api.services.sheets.v4.model.BatchGetValuesResponse;
25+
import com.google.auth.http.HttpCredentialsAdapter;
26+
import com.google.auth.oauth2.GoogleCredentials;
27+
28+
import java.io.IOException;
29+
import java.util.Collections;
30+
import java.util.List;
31+
32+
/* Class to demonstrate the use of Spreadsheet Batch Get Values API */
33+
public class BatchGetValues {
34+
/**
35+
* Returns one or more ranges of values from a spreadsheet.
36+
*
37+
* @param spreadsheetId - Id of the spreadsheet.
38+
* @param ranges - Range of cells of the spreadsheet.
39+
* @return Values in the range
40+
* @throws IOException - if credentials file not found.
41+
*/
42+
public static BatchGetValuesResponse batchGetValues(String spreadsheetId,
43+
List<String> ranges)
44+
throws IOException {
45+
/* Load pre-authorized user credentials from the environment.
46+
TODO(developer) - See https://developers.google.com/identity for
47+
guides on implementing OAuth2 for your application. */
48+
GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
49+
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
50+
HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
51+
credentials);
52+
53+
// Create the sheets API client
54+
Sheets service = new Sheets.Builder(new NetHttpTransport(),
55+
GsonFactory.getDefaultInstance(),
56+
requestInitializer)
57+
.setApplicationName("Sheets samples")
58+
.build();
59+
60+
BatchGetValuesResponse result = null;
61+
try {
62+
// Gets the values of the cells in the specified range.
63+
result = service.spreadsheets().values().batchGet(spreadsheetId)
64+
.setRanges(ranges).execute() ;
65+
System.out.printf("%d ranges retrieved.", result.getValueRanges().size());
66+
} catch (GoogleJsonResponseException e) {
67+
// TODO(developer) - handle error appropriately
68+
GoogleJsonError error = e.getDetails();
69+
if (error.getCode() == 404) {
70+
System.out.printf("Spreadsheet not found with id '%s'.\n",spreadsheetId);
71+
} else {
72+
throw e;
73+
}
74+
}
75+
return result;
76+
}
77+
}
78+
// [END sheets_batch_get_values]

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

Lines changed: 92 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,92 @@
1+
// Copyright 2022 Google LLC
2+
//
3+
// Licensed under the Apache License, Version 2.0 (the "License");
4+
// you may not use this file except in compliance with the License.
5+
// You may obtain a copy of the License at
6+
//
7+
// https://www.apache.org/licenses/LICENSE-2.0
8+
//
9+
// Unless required by applicable law or agreed to in writing, software
10+
// distributed under the License is distributed on an "AS IS" BASIS,
11+
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
// See the License for the specific language governing permissions and
13+
// limitations under the License.
14+
15+
16+
// [START sheets_batch_update_values]
17+
import com.google.api.client.googleapis.json.GoogleJsonError;
18+
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
19+
import com.google.api.client.http.HttpRequestInitializer;
20+
import com.google.api.client.http.javanet.NetHttpTransport;
21+
import com.google.api.client.json.gson.GsonFactory;
22+
import com.google.api.services.sheets.v4.Sheets;
23+
import com.google.api.services.sheets.v4.SheetsScopes;
24+
import com.google.api.services.sheets.v4.model.BatchUpdateValuesRequest;
25+
import com.google.api.services.sheets.v4.model.BatchUpdateValuesResponse;
26+
import com.google.api.services.sheets.v4.model.ValueRange;
27+
import com.google.auth.http.HttpCredentialsAdapter;
28+
import com.google.auth.oauth2.GoogleCredentials;
29+
30+
import java.io.IOException;
31+
import java.util.ArrayList;
32+
import java.util.Collections;
33+
import java.util.List;
34+
35+
/* Class to demonstrate the use of Spreadsheet Batch Update Values API */
36+
public class BatchUpdateValues {
37+
/**
38+
* Set values in one or more ranges of spreadsheet.
39+
*
40+
* @param spreadsheetId - Id of the spreadsheet.
41+
* @param range - Range of cells of the spreadsheet.
42+
* @param valueInputOption - Determines how input data should be interpreted.
43+
* @param values - list of rows of values to input.
44+
* @return spreadsheet with updated values
45+
* @throws IOException - if credentials file not found.
46+
*/
47+
public static BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId,
48+
String range,
49+
String valueInputOption,
50+
List<List<Object>> values)
51+
throws IOException {
52+
/* Load pre-authorized user credentials from the environment.
53+
TODO(developer) - See https://developers.google.com/identity for
54+
guides on implementing OAuth2 for your application. */
55+
GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
56+
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
57+
HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
58+
credentials);
59+
60+
// Create the sheets API client
61+
Sheets service = new Sheets.Builder(new NetHttpTransport(),
62+
GsonFactory.getDefaultInstance(),
63+
requestInitializer)
64+
.setApplicationName("Sheets samples")
65+
.build();
66+
67+
List<ValueRange> data = new ArrayList<>();
68+
data.add(new ValueRange()
69+
.setRange(range)
70+
.setValues(values));
71+
72+
BatchUpdateValuesResponse result = null;
73+
try {
74+
// Updates the values in the specified range.
75+
BatchUpdateValuesRequest body = new BatchUpdateValuesRequest()
76+
.setValueInputOption(valueInputOption)
77+
.setData(data);
78+
result = service.spreadsheets().values().batchUpdate(spreadsheetId, body).execute();
79+
System.out.printf("%d cells updated.", result.getTotalUpdatedCells());
80+
} catch (GoogleJsonResponseException e) {
81+
// TODO(developer) - handle error appropriately
82+
GoogleJsonError error = e.getDetails();
83+
if (error.getCode() == 404) {
84+
System.out.printf("Spreadsheet not found with id '%s'.\n",spreadsheetId);
85+
} else {
86+
throw e;
87+
}
88+
}
89+
return result;
90+
}
91+
}
92+
// [END sheets_batch_update_values]

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

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,75 @@
1+
// Copyright 2022 Google LLC
2+
//
3+
// Licensed under the Apache License, Version 2.0 (the "License");
4+
// you may not use this file except in compliance with the License.
5+
// You may obtain a copy of the License at
6+
//
7+
// https://www.apache.org/licenses/LICENSE-2.0
8+
//
9+
// Unless required by applicable law or agreed to in writing, software
10+
// distributed under the License is distributed on an "AS IS" BASIS,
11+
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
// See the License for the specific language governing permissions and
13+
// limitations under the License.
14+
15+
16+
// [START sheets_get_values]
17+
import com.google.api.client.googleapis.json.GoogleJsonError;
18+
import com.google.api.client.googleapis.json.GoogleJsonResponseException;
19+
import com.google.api.client.http.HttpRequestInitializer;
20+
import com.google.api.client.http.javanet.NetHttpTransport;
21+
import com.google.api.client.json.gson.GsonFactory;
22+
import com.google.api.services.sheets.v4.Sheets;
23+
import com.google.api.services.sheets.v4.SheetsScopes;
24+
import com.google.api.services.sheets.v4.model.ValueRange;
25+
import com.google.auth.http.HttpCredentialsAdapter;
26+
import com.google.auth.oauth2.GoogleCredentials;
27+
28+
import java.io.IOException;
29+
import java.util.Collections;
30+
31+
/* Class to demonstrate the use of Spreadsheet Get Values API */
32+
public class GetValues {
33+
/**
34+
* Returns a range of values from a spreadsheet.
35+
*
36+
* @param spreadsheetId - Id of the spreadsheet.
37+
* @param range - Range of cells of the spreadsheet.
38+
* @return Values in the range
39+
* @throws IOException - if credentials file not found.
40+
*/
41+
public static ValueRange getValues(String spreadsheetId, String range) throws IOException {
42+
/* Load pre-authorized user credentials from the environment.
43+
TODO(developer) - See https://developers.google.com/identity for
44+
guides on implementing OAuth2 for your application. */
45+
GoogleCredentials credentials = GoogleCredentials.getApplicationDefault()
46+
.createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS));
47+
HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter(
48+
credentials);
49+
50+
// Create the sheets API client
51+
Sheets service = new Sheets.Builder(new NetHttpTransport(),
52+
GsonFactory.getDefaultInstance(),
53+
requestInitializer)
54+
.setApplicationName("Sheets samples")
55+
.build();
56+
57+
ValueRange result = null;
58+
try {
59+
// Gets the values of the cells in the specified range.
60+
result = service.spreadsheets().values().get(spreadsheetId, range).execute();
61+
int numRows = result.getValues() != null ? result.getValues().size() : 0;
62+
System.out.printf("%d rows retrieved.", numRows);
63+
} catch (GoogleJsonResponseException e) {
64+
// TODO(developer) - handle error appropriately
65+
GoogleJsonError error = e.getDetails();
66+
if (error.getCode() == 404) {
67+
System.out.printf("Spreadsheet not found with id '%s'.\n",spreadsheetId);
68+
} else {
69+
throw e;
70+
}
71+
}
72+
return result;
73+
}
74+
}
75+
// [END sheets_get_values]

0 commit comments

Comments
 (0)