Skip to content

Instantly share code, notes, and snippets.

@hmasum52

hmasum52/google_sheet_parser.dart

Last active May 17, 2022 08:15
Show Gist options
  • Save hmasum52/79a3761c0a00be6386245151793861bd to your computer and use it in GitHub Desktop.
Save hmasum52/79a3761c0a00be6386245151793861bd to your computer and use it in GitHub Desktop.
Download ZIP
Parse data from public(shared) google sheet in dart and flutter
Raw
google_sheet_parser.dart
import 'dart:convert';
import 'dart:developer';
import 'package:html/dom.dart' as dom;
import 'package:html/parser.dart';
import 'package:http/http.dart' as http;
class GoogleSheetParser {
/// Parses the google sheet and returns a list of maps
/// with the keys being the column names and the values being the values
/// in the column
/// Example:
/// [{'name': 'John', 'age': '30'}, {'name': 'Jane', 'age': '25'}]
/// If the sheet is empty, returns an empty list
/// If the sheet is not found, throws an exception
static Future<List<Map<String, String>>> parseSheet(String url) async {
/// fetch the public google sheet as html response
List<Map<String, String>> dataList = [];
try {
http.Response res = await http.Client().get(Uri.parse(url));
if (res.statusCode == 200) {
dom.Document body = parse(res.body);
dom.Element table = body.getElementsByTagName("table").first;
dom.Element tbody = table.getElementsByTagName("tbody").first;
List<String> header = [];
int r = 1;
for (dom.Element tr in tbody.getElementsByTagName("tr")) {
//StringBuffer sb = StringBuffer();
Map<String, String> data = {};
int c = 0;
for (dom.Element td in tr.getElementsByTagName("td")) {
if (td.text != "") {
//sb.write(td.text);
//sb.write(" ");
if (r == 1) {
header.add(td.text); // get the mapped header name
} else {
data[header[c]] = td.text;
}
c++;
}
}
/* if (sb.toString() != "") {
log(sb.toString());
} */
if (data.isNotEmpty) {
dataList.add(data);
}
r++;
}
} else {
throw MyExpection("Failed to fetch google sheet.");
}
} catch (e) {
throw MyExpection("Link is not valid.");
}
log("Data List: ");
log(json.encode(dataList));
return dataList;
}
}
class MyExpection implements Exception {
final String message;
MyExpection(this.message);
@override
String toString() {
return message;
}
}
Raw
parse-google-sheet.dart
/// author: https://github.com/hmasum52
import 'package:html/dom.dart' as dom;
import 'package:html/parser.dart';
import 'package:http/http.dart' as http;
void parseData() async {
String link =
"https://docs.google.com/spreadsheets/d/1Uxw3S3h3fZ_jJdSy3ejQWAV8m1ffvTn-C8qo8qDLiEU/edit#gid=0";
http.Response res = await http.Client().get(Uri.parse(link));
if (res.statusCode == 200) {
dom.Document body = parse(res.body);
dom.Element table = body.getElementsByTagName("table").first;
dom.Element tbody = table.getElementsByTagName("tbody").first;
for (dom.Element tr in tbody.getElementsByTagName("tr")) {
StringBuffer sb = StringBuffer();
for (dom.Element td in tr.getElementsByTagName("td")) {
if (td.text != "") {
sb.write(td.text);
sb.write(" ");
}
}
if (sb.toString() != "") {
print(sb.toString());
}
}
}
}
class GoogleSheetParser {
String url;
GoogleSheetParser(this.url);
/// Parses the google sheet and returns a list of maps
/// with the keys being the column names and the values being the values
/// in the column
/// Example:
/// [{'name': 'John', 'age': '30'}, {'name': 'Jane', 'age': '25'}]
/// If the sheet is empty, returns an empty list
/// If the sheet is not found, throws an exception
Future<List<Map<String, dynamic>>> parseSheet() async {
/// fetch the public google sheet as html response
http.Response res = await http.Client().get(Uri.parse(url));
if (res.statusCode == 200) {
dom.Document body = parse(res.body);
dom.Element table = body.getElementsByTagName("table").first;
dom.Element tbody = table.getElementsByTagName("tbody").first;
for (dom.Element tr in tbody.getElementsByTagName("tr")) {
StringBuffer sb = StringBuffer();
for (dom.Element td in tr.getElementsByTagName("td")) {
if (td.text != "") {
sb.write(td.text);
sb.write(" ");
}
}
if (sb.toString() != "") {
//print(sb.toString());
}
}
}else{
throw Exception("Failed to fetch google sheet.");
}
return [];
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment