nrSpreadsheetWrapper.js

/**
*   @name: Google Spreadsheet Wrapper
*   @version: 1.0.0
*   @author: Alexander Groß
*   norisk GmbH
*   agross@noriskshop.de
*
*   Spreadsheet Wrapper that you can use to administrate a database of multiple spreadsheets. This library shows how a wrapper can work.
*   IMPORTANT: You will need to implement the abstract method 
*   
*/

/**
* Required global variables.
*/
var ELEMENT_IDENTIFIER = "TEST" // Name/Identifier of the element you need the Spreadsheet service for. This is used to fetch the config file from your drive.
var SPREADSHEET_ARRAY = []; // Empty array where the spreadsheets will get stored after JSON initialization.
var SPREADSHEET_POINTER = 0; // Pointer to the current SPREADSHEET_ARRAY index.
var SPREADSHEET; // Global variable where a reference to the current active spreadsheet is stored after database initialization.

//Example main()-function:
function main(){
	//... some code before you need the spreadsheets
	var existingValues = []; // Maybe you want to compare two sets of data later, this variable will hold the existing values from your database.
	var inputData = []; // Here you can store the values you want to compare your existing data to, maybe a product feed.
	var valuesToCreate = []; // Here new values will get stored.
	var spreadsheet_wrapper; //This variable will store our spreadsheet wrapper object to handle our data.
	
	spreadsheet_wrapper = new nrSpreadsheetWrapper();
	spreadsheet_wrapper.database_init(); // Here we initialize the database from a previously created JSON-file in your drive (see below). If this is the first run said json file will be created.
	
	//Set the global spreadsheet object to the spreadsheet currently pointed to. 
	//The wrapper always starts at index 0. In case appending new rows fails, the pointer will get moved to the next index. 
	//This makes sure the appending process is carried out.
	SPREADSHEET = SpreadsheetApp.openById(SPREADSHEET_ARRAY[SPREADSHEET_POINTER]).getActiveSheet(); 
	
	// Here you migth want to compare your spreadsheet data base to another data collection. 
	// Firstly, the wrapper loads the data from all spreadsheets and provides an array with the data. 
	existingValues = spreadsheet_wrapper.loadData();
	
	// Then you need to compare the two sets of data.
	valuesToCreate = spreadsheet_wrapper.compareValues(existingValues,inputData) // spreadsheet_wrapper.compareValues is an abstract function, you need to implement.
	
	// Here we will loop through all values to create and add them to the spreadsheet. Before that you might want to do stuff with the objects.
	for(var i = 0; i < valuesToCreate.length; i++){
		//... more code before you might add data to spreadsheet, for example entity creation.
		spreadsheet_wrapper.spreadsheetAppendRow([new Date(),valuesToCreate[i],i*i]);
	}
}

/**
* Constructor for the nrSpreadsheetWrapper-Object
* @constructor
*/
var nrSpreadsheetWrapper = function(){

/** 
* Function that is a wrapper for the Google Spreadsheet API function "Spreadsheet.appendRow()". It is also the controller for the spreadsheet which is supposed to be used.
* @return void
*/
	this.spreadsheetAppendRow = function(args){
  		try{
    		SPREADSHEET.appendRow(args);
  		}
  		catch(e){
    		if(SPREADSHEET_POINTER == (SPREADSHEET_ARRAY.length-1)){
      			createNewSpreadsheet();
      			SPREADSHEET_POINTER++;
      			SPREADSHEET = SpreadsheetApp.openById(SPREADSHEET_ARRAY[SPREADSHEET_POINTER]).getActiveSheet();
      			controlSpreadsheetAppendRow(args);
    		}
    		else{
      			SPREADSHEET_POINTER++;
      			SPREADSHEET = SpreadsheetApp.openById(CONTROL_SPREADSHEET_ARRAY[CONTROL_SPREADSHEET_POINTER]).getActiveSheet();
      			controlSpreadsheetAppendRow(args);
    		}
  		}
	};

/**
* Function that loads a json configuration file from your google drive and initializes the spreadsheets. Use this method to load the database when you need it. After the function call you need to initialize the spreadsheet.
* @return void
*/
	this.database_init = function(){
		var json_config_file, json_config, filename;
		
  		Logger.log("Initializing database...");
  		filename = AdWordsApp.currentAccount().getName() +"_"+ ELEMENT_IDENTIFIER +"_config.json";
		Logger.log("Fetching file...");
		
  		if(DriveApp.getFilesByName(filename).hasNext()){
    			json_config_file = DriveApp.getFilesByName(filename).next();
  		}
  		else{
    			json_config_file = DriveApp.createFile(filename,"");
  		}
  		json_config = json_config_file.getBlob().getDataAsString();

  		if(json_config){
			json_config = JSON.parse(json_config);
    			SPREADSHEET_ARRAY = json_config.spreadsheetIDs;
    			json_config.lastUpdateTime = new Date();
    			json_config_file.setContent(JSON.stringify(json_config));
  		}
  		else{
    			createNewSpreadsheetArray();
    			json_config = {
      				elementName : ELEMENT_IDENTIFIER,
      				spreadsheetIDs : SPREADSHEET_ARRAY,
      				initialLoadDate : new Date(),
      				lastUpdateTime : new Date()
    			};
    			json_config_file.setContent(JSON.stringify(json_config));
  		}
  		Logger.log("Database initialized. See the current state below: \n"+JSON.stringify(json_config)+"");
	};
	
/**
* Function that loads values from the spreadsheet database. You may want to adjust the code, so it fits your specific needs.
* IMPORTANT: CHANGE CODE IN LINE 126 TO ADJUST THE CELL RANGE YOU WANT TO GRAB.
* @return Array
*/
	this.loadData = function(){
		var existingValues = [];		
		try{
			for(var k=0;k<SPREADSHEET_ARRAY.length;k++){
				var that = SpreadsheetApp.openById(SPREADSHEET_ARRAY[k]).getActiveSheet();
				
				//IMPORTANT: CHANGE CODE IN LINE 126 TO ADJUST THE CELL RANGE YOU WANT TO GRAB.
				existingValues = existingValues.concat(that.getRange("A1:B"+that.getLastRow()).getValues());
    			}
  		}
		catch(e){
			Logger.log("No data in spreadsheet yet. @main()");
		}
		return existingValues;
	};
	
/**
* Abstract function that compares two sets of data and returns an array.
* YOU NEED TO IMPLEMENT THE FUNCTION SO IT FITS YOUR PURPOSE.
* @param Array existingValues 
* @param Array inputData
* @return Array
*/
	this.compareValues = function(existingValues,inputData){
		// implement code here. 
	};
/**
* Function that creates a new spreadsheet with the specific element identifier name and index whenever needed.
* @return void
*/
	function createNewSpreadsheet(){
  		var spreadsheet = SpreadsheetApp.create(AdWordsApp.currentAccount().getName() +"_"+ ELEMENT_IDENTIFIER +"_"+SPREADSHEET_ARRAY.length);
  		if(!spreadsheet){
    			throw "Fatal Spreadsheet Creation Exception. @createNewSpreadsheet";
		}
  		SPREADSHEET_ARRAY.push(spreadsheet.getId());
  		refreshJsonData();
	}
	
/**
* Function that updates the json data whenever changes are made.
* @return void
*/
	function refreshJsonData(){
	  	var accName, json_config_file, json_config;
	
		accName = AdWordsApp.currentAccount().getName();
		json_config_file = DriveApp.getFilesByName(accName + ELEMENT_IDENTIFIER+"_config.json").next();
		
	 	json_config = JSON.parse(json_config_file.getBlob().getDataAsString());
  		json_config.spreadsheetIDs = SPREADSHEET_ARRAY;
  		json_config.lastUpdateTime = new Date();
  		json_config = JSON.stringify(json_config);
  		json_config_file.setContent(json_config);
	}
/**
* Function that initializes the spreadsheet array upon database initialization.
* @return void
*/
	function createNewSpreadsheetArray(){
		if(SPREADSHEET_ARRAY.length == 0){
    		var spreadsheet = SpreadsheetApp.create(AdWordsApp.currentAccount().getName() +"_"+ ELEMENT_IDENTIFIER+"_0");
	    	if(!spreadsheet)
      			throw new Error("Fatal Spreadsheet Creation Exception. @createNewSpreadsheetArray");
    		SPREADSHEET_ARRAY.push(spreadsheet.getId());
  		}
	};
};

nrSpreadsheetWrapper.md

nrSpreadsheetWrapper.js

Simple AdWords Scripts library that administrates Google spreadsheets as database for you.

Installation

Copy the source code (from line 50) to the end of your script. Don't copy the example main function, this will mess up your script.

Required values

You will need four global variables in order for the script to work properly.

Modify this variable:
var ELEMENT_IDENTIFIER = "TEST" // Name/Identifier of the element you need the Spreadsheet service for. This is used to fetch the config file from your drive.

Don't modify those:
var SPREADSHEET_ARRAY = []; // Empty array where the spreadsheets will get stored after JSON initialization.
var SPREADSHEET_POINTER = 0; // Pointer to the current SPREADSHEET_ARRAY index.
var SPREADSHEET; // Global variable where a reference to the current active spreadsheet is stored after database initialization.

Wrapper methods

Public void

spreadsheetAppendRow()

Appends a new row to your database.

database_init()

Initializes the database.

Public Array

loadData()

Loads data from all spreadsheets into one array. IMPORTANT: YOU NEED TO CHANGE THE CODE IN LINE 126 TO ADJUST THE CELL RANGE YOU WANT TO GRAB.

Public abstract Array

compareValues(existingValues,inputData)

Compares two sets of data and returns an array. IMPORTANT: YOU NEED TO IMPLEMENT THE FUNCTION SO IT FITS YOUR PURPOSE.

---

You don't have to use the following private methods. This is just for your information:

Private void

createNewSpreadsheetArray()

Creates the first spreadsheet.

createNewSpreadsheet()

Creates a new spreadsheet.

refreshJsonData()

Refreshes the json file, during script execution.