Google spreadsheet row to columns example

 

This:

ToThis:

Run a script to go from the first spreadsheet to the format of the second see link. The code is a hard to find as it’s embedded a few layers into the document.

See https://developers.google.com/apps-script/guides/sheets#writing for information on how to setup and run this apps script, as it’s bit convoluted.

Download the spreadsheet here.

//takes row data and adds converts to column data

// for every band separate write the names in a new spreadsheet in the following format
// first name , last name, band name
// based off of https://developers.google.com/apps-script/guides/sheets#writing

//Paul Sobczak
//themountainfold.com
//tcmaker.org
//May 2013

function RunMe() {
//name of new spreadsheet
var second_sheet_name = ‘second sheet’;

//get the active spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// Get the range of cells that store data data.
var employeeDataRange = ss.getRangeByName(“EmployeeData”);

// For every row of employee data, generate an employee object.
var employeeObjects = getRowsData(sheet, employeeDataRange);
var j =0;
var a =0;
var m = 0;
var k = 0;
var index = 0;

//add keys to new spread sheet
var other = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
other.getSheetByName(second_sheet_name).getRange(1, 1).setValue(‘First Name’);
other.getSheetByName(second_sheet_name).getRange(1, 2).setValue(‘Last Name’);
other.getSheetByName(second_sheet_name).getRange(1, 3).setValue(‘Band Name’);

// split the comma seperated names into a new array
var band = employeeObjects[a];
var seperatedNamesBand = band.names.split(‘,’);

//for every band seperate write the names in a new spreadshet in the following format
// first name , last name, band name

while(employeeObjects[m] != null)
{
var band = employeeObjects[m];
var seperatedNamesBand = band.names.split(‘, ‘);
k = 0;
while(seperatedNamesBand[k] != null)
{
var seperatedName = seperatedNamesBand[k].split(‘ ‘);
other.getSheetByName(second_sheet_name).getRange(2+k+index, 1).setValue(seperatedName[0]);
other.getSheetByName(second_sheet_name).getRange(2+k+index, 2).setValue(seperatedName[1]);
other.getSheetByName(second_sheet_name).getRange(2+k+index, 3).setValue(band.bandName);
//other.getSheetByName(second_sheet_name).getRange(2+k+index, 4).setValue(m);
//other.getSheetByName(second_sheet_name).getRange(2+k+index, 5).setValue(k);
//other.getSheetByName(second_sheet_name).getRange(2+k+index, 6).setValue(index);
k++;
}
index= index +k;
m++;
}

}

// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   – sheet: the sheet object that contains the data to be processed
//   – range: the exact range of cells where the data is stored
//   – columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() – 1;
var numColumns = range.getLastColumn() – range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
var headers = headersRange.getValues()[0];
return getObjects(range.getValues(), normalizeHeaders(headers));
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   – data: JavaScript 2d array
//   – keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
if (isCellEmpty(cellData)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}

// Returns an Array of normalized Strings.
// Arguments:
//   – headers: Array of Strings to normalize
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
var key = normalizeHeader(headers[i]);
if (key.length > 0) {
keys.push(key);
}
}
return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   – header: string to normalize
// Examples:
//   “First Name” -> “firstName”
//   “Market Cap (millions) -> “marketCapMillions
//   “1 number at the beginning is ignored” -> “numberAtTheBeginningIsIgnored”
function normalizeHeader(header) {
var key = “”;
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == ” ” && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) {
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   – cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == “string” && cellData == “”;
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
return char >= ‘A’ && char <= ‘Z’ ||
char >= ‘a’ && char <= ‘z’ ||
isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
return char >= ‘0’ && char <= ‘9’;
}

Post a Comment

Your email is never shared. Required fields are marked *

*
*