Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A Google Apps Script for importing CSV data into a Google Spreadsheet.
// 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);
}
@chaseadam
Copy link

chaseadam commented Sep 15, 2016

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

@nicoferfio
Copy link

nicoferfio commented Dec 16, 2016

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!!!

@EmbDclic
Copy link

EmbDclic commented Sep 29, 2017

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!

@Stefano7777777
Copy link

Stefano7777777 commented Jan 26, 2021

hello, thankps for the scypt. But i need to import only certain column (column 1, 5, 20). What i have t do?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment