Сетевой граф на основе формы Google: переструктурируем данные

Если Google Fusion Tables являются очень удобным средством визуализации данных, то одним из самых популярных средств сбора данных были и остаются формы Google. С их помощью мы можем быство собрать ответы на поставленные вопросы у десятков и сотен людей и сами Формы объединят эти ответы в таблицу. Действительно, было бы очень удобно собирать даные с помощью Формы и визуализировать их с помощью Fusion Table, используя для этого одну и ту же таблицу. Но здесь возникает проблема. Fusion Table может построить визуализацию, например, в виде графа сети, используя массив данных, подобранных в два столбца, один из которых - родительский элемент, а второй - дочерний (как, например, здесь). А Форма Google дает нам данные в “плоском” виде, записывая каждый новый ответ пользователя в новый столбец. В качестве примера возьмем результаты формы, в которой отвечающим предлагалось указать наиболее актуальные, с их точки зрения, темы сетевого взаимодействия и экспертов, обладающих уникальным знанием по этим темам.

Иными словами, для использования в Fusion Tables данные Формы нужно переструктурировать, записав их в два столбца. Делать это вручную, естественно, неудобно и неэффективно. Поэтому здесь нам на помощь приходит Google скрипт, который и проделывает эту операцию - берет данные в листе с ответами на вопросы Формы Google и записывает их в виде, удобном для визуализации в Fusion Tables.

Вот этот скрипт

	function restructure() {
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	//указываем на таблицу, в которой мы работаем
	var sourceSheet = ss.getSheetByName("Form Responses 1");
	//указываем на лист, из которого берем данные
	var targetSheet = ss.getSheetByName("Sheet2");
	//указываем на лист, в который записываем данные
	var headers = sourceSheet.getRange(1, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
	var rows = sourceSheet.getRange(1, 1, sourceSheet.getLastRow()).getValues();
	targetSheet.getRange("A1").setValue("source");
	targetSheet.getRange("B1").setValue("target");
	//указываем необходимые диапазоны данных
	var lastRow = targetSheet.getLastRow()
	for (j = 1; j < rows.length; j++) {
	var lastRow = targetSheet.getLastRow()
	for (i = 1; i < headers.length; i++) {
	//задаем цикл для прочтения данных в обоих листах
	switch (headers[i]) {
	case "Ваше имя":
	var target = targetSheet.getRange(lastRow+1, i);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Ключевая тема 1":
	var target = targetSheet.getRange(lastRow+1, i);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 1 Эксперт 1":
	var parTarget = targetSheet.getRange(lastRow+2,1);
	sourceSheet.getRange(j+1, i).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+2, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 1 Эксперт 2":
	var parTarget = targetSheet.getRange(lastRow+3,1);
	sourceSheet.getRange(j+1, i-1).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+3, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 1 Эксперт 3":
	var parTarget = targetSheet.getRange(lastRow+4,1);
	sourceSheet.getRange(j+1, i-2).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+4, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Ключевая тема 2":
	var parTarget = targetSheet.getRange(lastRow+5,1);
	sourceSheet.getRange (j+1, i-4).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+5,2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 2 Эксперт 1":
	var parTarget = targetSheet.getRange(lastRow+6,1);
	sourceSheet.getRange(j+1, i).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+6, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 2 Эксперт 2":
	var parTarget = targetSheet.getRange(lastRow+7,1);
	sourceSheet.getRange(j+1, i-1).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+7, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 2 Эксперт 3":
	var parTarget = targetSheet.getRange(lastRow+8,1);
	sourceSheet.getRange(j+1, i-2).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+8, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Ключевая тема 3":
	var parTarget = targetSheet.getRange(lastRow+9,1);
	sourceSheet.getRange (j+1, i-8).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+9,2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 3 Эксперт 1":
	var parTarget = targetSheet.getRange(lastRow+10,1);
	sourceSheet.getRange(j+1, i).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+10, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 3 Эксперт 2":
	var parTarget = targetSheet.getRange(lastRow+11,1);
	sourceSheet.getRange(j+1, i-1).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+11, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	case "Тема 3 Эксперт 3":
	var parTarget = targetSheet.getRange(lastRow+12,1);
	sourceSheet.getRange(j+1, i-2).copyTo(parTarget);
	var target = targetSheet.getRange(lastRow+12, 2);
	sourceSheet.getRange(j+1, i+1).copyTo(target);
	break;
	default:
	Browser.msgBox('При переносе данных произошла ошибка!', Browser.Buttons.OK_CANCEL);
	//запускаем процесс перенесения данных из первого листа во второй
	//условия, указанные в "case" должны точно соответствовать названиям столбцов
	}
	}
	}
	}

view raw restructure hosted with ❤ by GitHub

Вы можете проверить его на тестовом наборе данных, взяв за основу таблицу, прилагающуюся к форме. Для этого:

1. Скопируйте таблицу к себе на Диск

2. Зайдите в Инструменты - Редактор скриптов и запустите скрипт Restructure.

3. Авторизуйте скрипт.

4. После того, как скрипт будет исполнен, данные будут скопированы в Лист 2 таблицы в нужном для нас формате.

5. Запуститите приложение Fusion Tables и экспортируйте в него данные из Листа 2 вашей таблицы. При экспорте нужно указывать второй лист (Sheet 2).

6. Визуализируйте данные, как описано в публикации в виде сетевого графа. У вас получится примерно такая картинка.

Вы всегда можете доработать скрипт и приспособить его под свои варианты ответов формы.