-
-
Save ianlewis/8310540 to your computer and use it in GitHub Desktop.
// vim: ft=javascript: | |
/*jslint sloppy: true, vars: true, white: true, nomen: true, browser: true */ | |
/*global SpreadsheetApp, UiApp, UrlFetchApp, Utilities */ | |
/* | |
* A script to automate requesting data from an external url that outputs CSV data. | |
* | |
* Adapted from the Google Analytics Report Automation (magic) script. | |
* @author nickski15@gmail.com (Nick Mihailovski) | |
* @author ianmlewis@gmail.com (Ian Lewis) | |
*/ | |
var CSV_CONFIG = 'csvconfig'; | |
/* =========== Logging ======================= */ | |
/** | |
* The output text that should be displayed in the log. | |
* @private. | |
*/ | |
var logArray_; | |
/** | |
* Clears the in app log. | |
* @private. | |
*/ | |
function setupLog_() { | |
logArray_ = []; | |
} | |
/** | |
* Returns the log as a string. | |
* @returns {string} The log. | |
*/ | |
function getLog_() { | |
return logArray_.join('\n'); | |
} | |
/** | |
* Appends a string as a new line to the log. | |
* @param {String} value The value to add to the log. | |
*/ | |
function log_(value) { | |
logArray_.push(value); | |
var app = UiApp.getActiveApplication(); | |
var foo = app.getElementById('log'); | |
foo.setText(getLog_()); | |
} | |
/** | |
* Displays the log in memory to the user. | |
*/ | |
function displayLog_() { | |
var uiLog = UiApp.createApplication().setTitle('Report Status').setWidth(400).setHeight(500); | |
var panel = uiLog.createVerticalPanel(); | |
uiLog.add(panel); | |
var txtOutput = uiLog.createTextArea().setId('log').setWidth('400').setHeight('500').setValue(getLog_()); | |
panel.add(txtOutput); | |
SpreadsheetApp.getActiveSpreadsheet().show(uiLog); | |
} | |
function getOrCreateSheet_(sheet_name) { | |
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = activeSpreadsheet.getSheetByName(sheet_name); | |
if (!sheet) { | |
sheet = activeSpreadsheet.insertSheet(sheet_name, 0); | |
} | |
return sheet; | |
} | |
/** | |
* Create a Menu when the script loads. Adds a new csvconfig sheet if | |
* one doesn't exist. | |
*/ | |
function onOpen() { | |
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
// Add a menu. | |
activeSpreadsheet.addMenu( | |
"CSV Report", [ | |
{name: "Get Data", functionName: "getCSVData"}, | |
{name: "Create Report", functionName: "createCSVReport"} | |
] | |
); | |
getOrCreateSheet_(CSV_CONFIG); | |
} | |
/** | |
* Returns the trailing number on a string. For example the | |
* input: xxxx555 will return 555. Inputs with no trailing numbers | |
* return undefined. Trailing whitespace is not ignored. | |
* @param {string} input The input to parse. | |
* @resturns {number} The trailing number on the input as a string. | |
* undefined if no number was found. | |
*/ | |
function getTrailingNumber_(input) { | |
// Match at one or more digits at the end of the string. | |
var pattern = /(\d+)$/; | |
var result = pattern.exec(input); | |
if (result) { | |
// Return the matched number. | |
return result[0]; | |
} | |
return undefined; | |
} | |
/** | |
* Returns the values from 2 columns from the csvconfig sheet starting at | |
* colIndex, as key-value pairs. Key-values are only returned if they do | |
* not contain the empty string or have a boolean value of false. | |
* If the key is start-date or end-date and the value is an instance of | |
* the date object, the value will be converted to a string in yyyy-MM-dd. | |
* If the key is start-index or max-results and the type of the value is | |
* number, the value will be parsed into a string. | |
* @param {number} colIndex The column index to return values from. | |
* @return {object} The values starting in colIndex and the following column | |
as key-value pairs. | |
*/ | |
function getConfigsStartingAtCol_(sheet, colIndex) { | |
var config = {}, rowIndex, key, value; | |
var range = sheet.getRange(1, colIndex, sheet.getLastRow(), 2); | |
// The first cell of the first column becomes the name of the query. | |
config.query = range.getCell(1,1).getValue(); | |
for (rowIndex = 2; rowIndex <= range.getLastRow(); ++rowIndex) { | |
key = range.getCell(rowIndex, 1).getValue(); | |
value = range.getCell(rowIndex, 2).getValue(); | |
if (value) { | |
config[key] = value; | |
} | |
} | |
return config; | |
} | |
/** | |
* Returns an array of config objects. This reads the csvconfig sheet | |
* and tries to extract adjacent column names that end with the same | |
* number. For example Names1 : Values1. Then both columns are used | |
* to define key-value pairs for the coniguration object. The first | |
* column defines the keys, and the adjacent column values define | |
* each keys values. | |
* @param {Sheet} The csvconfig sheet from which to read configurations. | |
* @returns {Array} An array of API query configuration object. | |
*/ | |
function getConfigs_(sheet) { | |
var configs = [], colIndex; | |
// There must be at least 2 columns. | |
if (sheet.getLastColumn() < 2) { | |
return configs; | |
} | |
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn()); | |
var firstColValue, firstColNum, secondColValue, secondColNum; | |
// Test the name of each column to see if it has an adjacent column that ends | |
// in the same number. ie xxxx555 : yyyy555. | |
// Since we check 2 columns at a time, we don't need to check the last column, | |
// as there is no second column to also check. | |
for (colIndex = 1; colIndex <= headerRange.getNumColumns() - 1; ++colIndex) { | |
firstColValue = headerRange.getCell(1, colIndex).getValue(); | |
firstColNum = getTrailingNumber_(firstColValue); | |
secondColValue = headerRange.getCell(1, colIndex + 1).getValue(); | |
secondColNum = getTrailingNumber_(secondColValue); | |
if (firstColNum && secondColNum && firstColNum === secondColNum) { | |
configs.push(getConfigsStartingAtCol_(sheet, colIndex)); | |
} | |
} | |
return configs; | |
} | |
// This will parse a delimited string into an array of | |
// arrays. The default delimiter is the comma, but this | |
// can be overriden in the second argument. | |
function parseCsvResponse_( strData, strDelimiter ){ | |
// Check to see if the delimiter is defined. If not, | |
// then default to comma. | |
strDelimiter = (strDelimiter || ","); | |
strData = strData.replace(/^\s+|\s+$/g, ''); | |
// Create a regular expression to parse the CSV values. | |
var objPattern = new RegExp( | |
( | |
// Delimiters. | |
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" + | |
// Quoted fields. | |
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" + | |
// Standard fields. | |
"([^\"\\" + strDelimiter + "\\r\\n]*))" | |
), | |
"gi" | |
); | |
// Create an array to hold our data. Give the array | |
// a default empty first row. | |
var arrData = [[]]; | |
// Create an array to hold our individual pattern | |
// matching groups. | |
var arrMatches = null; | |
// Keep looping over the regular expression matches | |
// until we can no longer find a match. | |
while (arrMatches = objPattern.exec( strData )){ | |
// Get the delimiter that was found. | |
var strMatchedDelimiter = arrMatches[ 1 ], strMatchedValue; | |
// Check to see if the given delimiter has a length | |
// (is not the start of string) and if it matches | |
// field delimiter. If id does not, then we know | |
// that this delimiter is a row delimiter. | |
if ( | |
strMatchedDelimiter.length && | |
(strMatchedDelimiter !== strDelimiter) | |
){ | |
// Since we have reached a new row of data, | |
// add an empty row to our data array. | |
arrData.push( [] ); | |
} | |
// Now that we have our delimiter out of the way, | |
// let's check to see which kind of value we | |
// captured (quoted or unquoted). | |
if (arrMatches[ 2 ]){ | |
// We found a quoted value. When we capture | |
// this value, unescape any double quotes. | |
strMatchedValue = arrMatches[ 2 ].replace( | |
new RegExp( "\"\"", "g" ), | |
"\"" | |
); | |
} else { | |
// We found a non-quoted value. | |
strMatchedValue = arrMatches[ 3 ]; | |
} | |
// Now that we have our value string, let's add | |
// it to the data array. | |
arrData[ arrData.length - 1 ].push( strMatchedValue ); | |
} | |
// Return the parsed data. | |
return arrData; | |
} | |
function populateSheetWithCSV_(sheet, csvUrl, user, pw) { | |
// request the CSV! | |
var headers = {}; | |
if (user && pw) { | |
headers = { | |
// use basic auth | |
'Authorization': 'Basic ' + Utilities.base64Encode( | |
user + ':' + pw, Utilities.Charset.UTF_8) | |
}; | |
} | |
var resp = UrlFetchApp.fetch(csvUrl, { | |
headers: headers, | |
validateHttpsCertificates: false // No valid certificate yet :( | |
}); | |
// parse the response as a CSV | |
var csvContent = parseCsvResponse_(resp.getContentText()); | |
// clear everything in the sheet | |
sheet.clearContents().clearFormats(); | |
// set the values in the sheet (as efficiently as we know how) | |
sheet.getRange( | |
1, 1, | |
csvContent.length, /* rows */ | |
csvContent[0].length /* columns */).setValues(csvContent); | |
} | |
/* | |
Gets the CSV data for each report and loads it into the appropriate sheet. | |
*/ | |
function getCSVData(e) { | |
setupLog_(); | |
var now = new Date(), i, config, configName, sheet; | |
log_('Running on: ' + now); | |
var configs = getConfigs_(getOrCreateSheet_(CSV_CONFIG)); | |
if (!configs.length) { | |
log_('No report configurations found'); | |
} else { | |
log_('Found ' + configs.length + ' report configurations.'); | |
for (i = 0; config = configs[i]; ++i) { | |
configName = config.query; | |
if (config['sheet-name']) { | |
if (config.url) { | |
try { | |
log_('Getting CSV: ' + configName); | |
sheet = getOrCreateSheet_(config['sheet-name']); | |
populateSheetWithCSV_(sheet, config.url, config['http-username'], config['http-password']); | |
} catch (error) { | |
log_('Error executing ' + configName + ': ' + error.message); | |
} | |
} else { | |
log_('No URL found: ' + configName); | |
} | |
} else { | |
log_('No sheet-name found: ' + configName); | |
} | |
} | |
} | |
log_('Script done'); | |
// Update the user about the status of the queries. | |
if( e === undefined ) { | |
displayLog_(); | |
} | |
} | |
/** | |
* Returns 1 greater than the largest trailing number in the header row. | |
* @param {Object} sheet The sheet in which to find the last number. | |
* @returns {Number} The next largest trailing number. | |
*/ | |
function getLastNumber_(sheet) { | |
var maxNumber = 0; | |
var lastColIndex = sheet.getLastColumn(); | |
if (lastColIndex > 0) { | |
var range = sheet.getRange(1, 1, 1, lastColIndex); | |
for (var colIndex = 1; colIndex < sheet.getLastColumn(); ++colIndex) { | |
var value = range.getCell(1, colIndex).getValue(); | |
var headerNumber = getTrailingNumber_(value); | |
if (headerNumber) { | |
var number = parseInt(headerNumber, 10); | |
maxNumber = number > maxNumber ? number : maxNumber; | |
} | |
} | |
} | |
return maxNumber + 1; | |
} | |
/** | |
* Adds a CSV Report configuration to the spreadsheet. | |
*/ | |
function createCSVReport() { | |
var sheet = getOrCreateSheet_(CSV_CONFIG); | |
var headerNumber = getLastNumber_(sheet); | |
var config = [ | |
["query" + headerNumber, "value" + headerNumber], | |
['url', ''], | |
['http-username', ''], | |
['http-password', ''], | |
['sheet-name', '']]; | |
sheet.getRange(1, sheet.getLastColumn() + 1, config.length, 2).setValues(config); | |
} |
Another approach Batch import CSV to a Spreadsheet
Can you provide a license on your file?
Can you share how you viewed the script behind "Google Analytics Report Automation (magic) script"? Presumably this is the same script as this add-on: https://chrome.google.com/webstore/detail/google-analytics/fefimfimnhjjkomigakinmjileehfopp
Hi, Ian,
I am really in need of your script, but I am unable to make it run it seems. Whenever I use the menu you create on the google sheets interface, I get a Report Status with the following:
Running on: Fri Dec 16 2016 13:40:01 GMT+0100 (CET)
No report configurations found
Script done
And nothing happens afterwards. What am I doing wrong? THis is the first time I am using GOogle Scripts, so forgive my ignorance and thank you for any help!!!
Hi,
Thank you for this script. It's great!
I would like to keep the cell styling (bold, background color, etc.), and only update the value when I click on "Get Data". Is there a simple line of code to change, or does it seem more complicated?
Thanks!
hello, thankps for the scypt. But i need to import only certain column (column 1, 5, 20). What i have t do?
The script break due to permission issue on all of the sheet modification call such as clear content or add new content. Did you have this issue?