Google таблицы как база данных для веб-приложения. Часть 3

В предыдущих двух частях данной статьи мы рассмотрели все основные вопросы, необходимые для того, чтобы с нуля создать и опубликовать веб приложение, которое умеет создавать, читать, обновлять и удалять элементы в Google таблицах. Этого должно быть достаточно для приложения, подобного тому, что мы создавали с вами в процессе. Однако, если вам потребуется получить не все строки, а только некоторые, то придется пробегать по массиву в цикле, выбирая нужные. Тоже самое относится к группировке и сортировке строк. Все это придется реализовывать самостоятельно.

Внимательный читатель вспомнит, что в первой части статьи мы говорили о том, что есть два способа получения данных из таблиц. В завершающей части статьи мы в основном поговорим как раз о втором способе работы с выборкой данных, который по совместительству является еще и более быстрым, красивым и удобным.

Данный способ подразумевает использование сторонней библиотеки (правда, от Google) на стороне клиента и она поддерживает SQL-подобный синтаксис. Я говорю о Google Charts, API которых можно использовать для своих нужд.

Доп. настройки таблицы

Для того чтобы наши запросы нормально отрабатывали нам необходимо сделать следующие вещи:

  1. В таблице первой строкой прописать заголовки столбцов, иначе первая строка после выполнения запроса будет помещаться в объект, хранящий заголовки (у нас это уже сделано).
  2. Открыть таблицу для просмотра, а именно установить статус “Просматривать могут все у кого есть ссылка”. К сожалению, это обязательное условие.
  3. Скопировать уникальный идентификатор таблицы.

Подключение библиотеки

Для подключения библиотеки достаточно в файле index.html перед ссылкой на скрипт main.js добавить две следующие строки:

<!-- подключаем для работы Google Charts -->
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

Взаимодействие с таблицей через API Google Charts

Для начала добавим еще одну глобальную переменную, которая будет содержать в себе уникальный идентификатор нашей таблицы.

var tasks = "1M1OKuXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXWj6Bs";//уникальный идентификатор нашей таблицы

Пишем новую функцию (вообще-то парочку…) для получения данных из таблицы:

function loadTasks (query) {
	googleQuery (tasks, '0', 'A:C', "SELECT * ORDER BY A DESC");
	//Эту функцию создал я для удобства. Она принимает следующие параметры по порядку:
	//1. Уникальный идентификатор таблицы;
	//2. Числовой идентификатор листа. По умолчанию у первого листа таблицы после ее 
             создания он равен нулю. 
	//   При создании других листов генерируется 8-ми значный числовой идентификатор. 
	//   Увидеть его можно в адресной строке в параметре "gid" (например, 
             "gid=99808602").
	//3. Столбцы, в которых будет осуществляться поиск согласно запросу.
	//4. Текст запроса в SQL-подобном формате. Обратите внимание, что формат все-таки 
             отличается от SQL.
}

function googleQuery (sheet_id, sheet, range, query) {
	google.charts.load('45', {'packages':['corechart']});//загружаем библиотеку Google Charts
	google.charts.setOnLoadCallback (queryTable);//обозначаем, какая функция будет 
        запущена по готовности библиотеки

	function queryTable () {
		//объект с настройками
		var opts = {sendMethod: 'auto'};
		//сама функция, выполняющая запрос к таблице
		var gquery = new google.visualization.Query('https://docs.google.com/spreadsheets/d/'+sheet_id+'/gviz/tq?gid='+sheet+'&range='+range+'&headers=1&tq='+query, opts);
		//обозначаем, какая функция будет запущена при получении результатов
		gquery.send (callback);
	}

	function callback (e) {
		if (e.isError () ) {
			console.log('Error in query: ' + e.getMessage () + ' ' + e.getDetailedMessage ());
			return;
		}//если ошибка, то записываем ее в консоль

		var data = e.getDataTable ();//если ошибки нет, то формируем данные для 
                дальнейшей работы
		tasksTable (data); //передаем их в функцию, которая обработает данные и сформирует из них нашу таблицу
	}
}

Модифицируем функцию вывода списка задач:

//функция, которая обработает данные, полученные при выполнении запроса и сформирует из них таблицу
function tasksTable (data) {
	$('#tasksTableDiv').html(function(){
		//внутри объекта data находятся еще два объекта:
		//Sf - хранит в себе данные из первой строки таблицы, принимая их за заголовки
		//Tf - хранит в себе данные строк таблицы в виде массива
		
		var th = '';
		for (i = 0; i < data.Sf.length; i++){
			//формируем заголовки таблицы
			th += '<th>'+data.Sf[i].label+'</th>';
		}

		th ='<tr>'+th+'<th>Удалить</th></tr>';

		var tr = '';
		for ( i = 0; i < data.Tf.length; i++ ) {
			var status = ( data.Tf[i].c[2].v == 0 ) ? 'В очереди' : 'Выполнена';
			var color = ( data.Tf[i].c[2].v == 1 ) ? 'class="table-success"' : '';
			//внутри "c" (content) может содержаться два значения:
			//v - само значение (value)
			//f - значение в форматированном виде. Время мы будем брать именно отсюда
			
			//формируем содержание таблицы
			tr += '<tr '+color+'>'+
						'<td>'+data.Tf[i].c[0].f+'</td>'+
						'<td>'+data.Tf[i].c[1].v+'</td>'+
						'<td><button type="button" class="btn btn-link" onclick="updateTaskModal(\''+data.Tf[i].c[1].v+'\', \''+data.Tf[i].c[2].v+'\', \'status\')">'+status+'</button></td>'+
						'<td><button type="button" class="btn btn-link" onclick="deleteTask(\''+data.Tf[i].c[1].v+'\')">Удалить</button></td>'+
					'</tr>';
		}
		//формируем и возвращаем готовую таблицу
		return '<table class="table"><thead>'+th+'</thead><tbody>'+tr+'</tbody></table>'
	})
}

Запускаем новую функцию при готовности страницы и (!) каждый раз после добавления, удаления или обновления строк в таблице.

$( document ).ready(function() {//функция запускается, как только страница будет готова для просмотра пользователю
	loadTasks ();
});

Проверяем результат:

На первый взгляд ничего не изменилось, но это не так:

  1. Формат вывода даты стал более привычным.
  2. Запрос через API Google Charts работает в разы быстрее, чем тот, что написали мы в первой части.
  3. Мы получили мощный инструмент получения данных из таблиц используя SQL подобный синтаксис! Если мы захотим получить задачи только со статусом «В очереди», то нам достаточно просто изменить «SELECT * ORDER BY A DESC» на «SELECT * WHERE C = 0 ORDER BY A DESC».

Имеется поддержка таких команд как SUM(), COUNT(), GROUP BY, ORDER BY, PIVOT, OFFSET, LIMIT, LABEL, FORMAT, OPTIONS. Используя их, из таблиц можно получать данные практически в любых представлениях и формах.

Конечно, данный инструмент не создавался как аналог полноценным СУБД и еще очень очень сильно отстает от любых из них по числу возможностей и, конечно, скорости работы. Однако его вполне можно использовать, если есть такая потребность. Например, если перед вами стоит задача просто вывести на сайте какие-то данные из Google таблиц, то можно смело опускать все, что написано в предыдущих частях данной статьи. Вам потребуется только подключить библиотеку, открыть таблицу (или ее копию) для просмотра и написать пару несложных скриптов на JS для обращения и вывода данных.

Заключение

На этом данную статью из трех частей я считаю завершенной, чего нельзя сказать о нашем приложении.

Что можно улучшить:

  • Для красоты ради мы можем добавить шапку и подвал, а также разместить пару контроллов для фильтрации выводимых данных.
  • Мы можем вынести логику GET и POST запросов в отдельную функцию.
  • Оставить только одно модальное окно и генерировать его содержание в зависимости от потребностей.
  • Можем переписать наш код и заменить древнюю некрасивую конкатенацию строк на более современный вариант с обратными кавычками стандарта ES6.
  • При удалении или обновлении строк, нам необязательно обновлять всю таблицу целиком, ведь нам ничего не мешает убрать элемент из DOM или налету заменить в нем какие-то значения, атрибуты или стили.

Финальная версия данного проекта, включающая небольшие косметические доработки, опубликована по следующей ссылке: tasks.atonko.ru