Delete Your Tweets

auth.gs

const API_KEY = PropertiesService.getScriptProperties().getProperty('apiKey')
const API_SECRET = PropertiesService.getScriptProperties().getProperty('apiKeySecret')
const ACCESS_TOKEN = PropertiesService.getScriptProperties().getProperty('accessToken')
const ACCESS_TOKEN_SECRET = PropertiesService.getScriptProperties().getProperty('accessTokenSecret')
const CLIENT_ID = PropertiesService.getScriptProperties().getProperty('clientId')
const CLIENT_SECRET = PropertiesService.getScriptProperties().getProperty('clientSecret')

/**
 * Authorizes and makes a request to the Twitter API v2
 * OAuth 2.0 Making requests on behalf of users
 * https://developer.twitter.com/en/docs/authentication/oauth-2-0/user-access-token
 */
function run() {
  var service = getService_();
  if (service.hasAccess()) {
    // https://developer.twitter.com/en/docs/twitter-api/users/lookup/api-reference/get-users-by-username-username
    var url = `https://api.twitter.com/2/users/by/username/workspacedevs?user.fields=verified`;
    var response = UrlFetchApp.fetch(url, {
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      },
      muteHttpExceptions: true
    });
    var result = JSON.parse(response.getContentText());
    Logger.log(JSON.stringify(result, null, 2));
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
        authorizationUrl);
  }
}

/**
 * Reset the authorization state, so that it can be re-tested.
 */
function reset() {
  getService_().reset();
  PropertiesService.getUserProperties().deleteProperty('code_challenge');
  PropertiesService.getUserProperties().deleteProperty('code_verifier');
}

/**
 * Configures the service.
 */
function getService_() {
  pkceChallengeVerifier();
  var userProps = PropertiesService.getUserProperties();
  return OAuth2.createService('Twitter')
  // Set the endpoint URLs.
      .setAuthorizationBaseUrl('https://twitter.com/i/oauth2/authorize')
      .setTokenUrl(
          'https://api.twitter.com/2/oauth2/token?code_verifier=' + userProps.getProperty('code_verifier'))

  // Set the client ID and secret.
      .setClientId(CLIENT_ID)
      .setClientSecret(CLIENT_SECRET)

  // Set the name of the callback function that should be invoked to
  // complete the OAuth flow.
      .setCallbackFunction('authCallback')

  // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(userProps)

  // Set the scopes to request (space-separated for Twitter services).
      .setScope('users.read tweet.read offline.access tweet.write')

  // Add parameters in the authorization url
      .setParam('response_type', 'code')
      .setParam('code_challenge_method', 'S256')
      .setParam('code_challenge', userProps.getProperty('code_challenge'))

      .setTokenHeaders({
        'Authorization': 'Basic ' + Utilities.base64Encode(CLIENT_ID + ':' + CLIENT_SECRET),
        'Content-Type': 'application/x-www-form-urlencoded'
      });
}

/**
 * Handles the OAuth callback.
 */
function authCallback(request) {
  var service = getService_();
  var authorized = service.handleCallback(request);
  if (authorized) {
    return HtmlService.createHtmlOutput('Success!');
  } else {
    return HtmlService.createHtmlOutput('Denied.');
  }
}

/**
 * Logs the redict URI to register.
 */
function logRedirectUri() {
  Logger.log(OAuth2.getRedirectUri());
}

/**
 * Generates code_verifier & code_challenge for PKCE
 */
function pkceChallengeVerifier() {
  var userProps = PropertiesService.getUserProperties();
  if (!userProps.getProperty('code_verifier')) {
    var verifier = '';
    var possible = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-._~';

    for (var i = 0; i < 128; i++) {
      verifier += possible.charAt(Math.floor(Math.random() * possible.length));
    }

    var sha256Hash = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, verifier);

    var challenge = Utilities.base64Encode(sha256Hash)
        .replace(/\+/g, '-')
        .replace(/\//g, '_')
        .replace(/=+$/, '');
    userProps.setProperty('code_verifier', verifier);
    userProps.setProperty('code_challenge', challenge);
  }
}

code.gs

function deleteTweet(service, id) {
  if (service.hasAccess()) {
    // https://developer.twitter.com/en/docs/twitter-api/users/lookup/api-reference/get-users-by-username-username
    var url = `https://api.twitter.com/2/tweets/`;
    var response = UrlFetchApp.fetch(url + id, {
      method:'DELETE',
      'contentType': 'application/json',
      headers: {
        Authorization: 'Bearer ' + service.getAccessToken()
      },
      muteHttpExceptions: true,
    });
    var result = JSON.parse(response.getContentText());
    if(result.status === 429) {
      console.error(result)
      return false
    } else {
      console.info(result)
      return result.data;
    }
  } else {
    var authorizationUrl = service.getAuthorizationUrl();
    Logger.log('Open the following URL and re-run the script: %s',
      authorizationUrl);
  }
}

function deleteTweets() {
  const service = getService_();
  const sheetName = 'tweets';
  const headerRow = 1;
  const rateLimit = 50;
  
  let counter = 0;

  // Get the table data from the specified sheet
  let table = getTable(sheetName, headerRow);

  // Iterate through each item in the table
  for (var i = 0; i < table.items.length; i++) {
    let item = table.items[i];
    let deleted_at = item.getFieldValue('deleted_at');
    let id = item.getFieldValue('id')

    // Check if the item is ready for processing based on status and timestamp and other item properties
    if (counter < rateLimit && deleted_at === '') {
      const result = deleteTweet(service, id)

      if (!result) {
        // end loop because api error
        counter = rateLimit
      } else {
        // Update timestamp fields
        const deleted_at = getCurrentTime();

        console.info(`Tweet ${id} deleted at: `, deleted_at)
        item.setFieldValue('deleted_at', deleted_at);

        counter++;

        // Commit the changes to the item
        item.commit();
      }
    }
  }
}



function getCurrentTime() {
  var now = new Date();
  var year = now.getFullYear();
  var month = (now.getMonth() + 1).toString().padStart(2, '0'); // Adding 1 because months are zero-based
  var day = now.getDate().toString().padStart(2, '0');
  var hours = now.getHours().toString().padStart(2, '0');
  var minutes = now.getMinutes().toString().padStart(2, '0');
  var seconds = now.getSeconds().toString().padStart(2, '0');
  
  var formattedTime = year + "-" + month + "-" + day + " " + hours + ":" + minutes + ":" + seconds;
  
  return formattedTime;
}

item.gs

/**
 * Constructor for an item in a Table object.
 * @param {Number} i: id/order of the item in the Table frame. Start at 0 (first item in grid).
 * @param {Range} range: the grid range the item is from.
 * @param {Array} header: The header array.
 * @constructor
 */
function Item(i, header, row, column, sheet) {
  this.fields = {};
  
  this.table = {};
  this.table.header = header;
  this.table.row = row;
  this.table.column = column;
  this.table.sheet = sheet;
  
  this.i = i;
  this.authorizedToCommit = true;
}

/**
 * Method to add a new field to the item, given a value, note, background, formula and font color.
 * @param {String} label: The name of the field.
 * @param {String|Number|Date} value: The value from a cell.
 * @param {String} note: The note from a cell.
 * @param {String} background: The background color of a cell (can be string for basic colors or hex code string).
 * @param {String} formula: The R1C1 format formula.
 * @param {String} font: The font color.
 */
Item.prototype.addField = function(label, value, note, background, formula, font) {
  this.fields[label] = {"value": value, "note": note, "background": background, "formula": formula, "font": font};
  for (var param in this.fields[label]) {
    if (this.fields[label][param] === undefined) {
      this.fields[label][param] = "";
    }
  }
};

/**
 * Method to convert the item into a JS object, with its attributes being the header fields of the item.
 * @return {Object} itemObject: The object with each of the header/fieldValue pairs of the item.
 */
Item.prototype.toObject = function() {
  var self = this;
  return this.table.header.reduce( function(acc, headerField) {
    var fieldValue = self.getFieldValue(headerField);
    acc[headerField] = fieldValue;
    return acc;
  }, {});
};

/**
 * Commit a single item line in spreadsheet if the items order has not been changed since instantiating the grid.
 */
Item.prototype.commit = function () {
  if (!(this.authorizedToCommit)) {
    throw "Forbidden to commit this item. The order of the grid it is associated to has changed or it has been deleted."
  }

  var rowValues = [];
  var rowNotes = [];
  var rowBackgrounds = [];
  var rowWraps = [];
  var rowFontColors = [];

  for (var j = 0; j < this.table.header.length; j++) {
    var field = this.table.header[j];
    var value = this.getFieldValue(field);
    var formula = this.getFieldFormula(field);

    (formula)? rowValues.push(formula) : rowValues.push(value);
    rowNotes.push(this.getFieldNote(field));
    rowBackgrounds.push(this.getFieldBackground(field));
    rowWraps.push(false);
    rowFontColors.push(this.getFieldFontColor(field))
  }

  var lineRange = this.getLineRange();
  lineRange.setValues([rowValues]);
  lineRange.setNotes([rowNotes]);
  lineRange.setBackgrounds([rowBackgrounds]);
  lineRange.setWraps([rowWraps]);
  lineRange.setFontColors([rowFontColors]);
};


/**
 * Commit a whole item values. Disregarded other dimensions.
 */
Item.prototype.commitValues = function () {
  if (!(this.authorizedToCommit)) {
    throw "Forbidden to commit this item. The order of the grid it is associated to has changed or it has been deleted."
  }

  var rowValues = [];

  for (var j = 0; j < this.table.header.length; j++) {
    var field = this.table.header[j];
    var value = this.getFieldValue(field);
    var formula = this.getFieldFormula(field);

    (formula)? rowValues.push(formula) : rowValues.push(value);
  }

  var lineRange = this.getLineRange();
  lineRange.setValues([rowValues]);
};


/**
 * Commit a whole item backgrounds. Disregarded other dimensions.
 */
Item.prototype.commitBackgrounds = function () {
  if (!(this.authorizedToCommit)) {
    throw "Forbidden to commit this item. The order of the grid it is associated to has changed."
  }
  var rowBackgrounds = [];
  for (var j = 0; j < this.table.header.length; j++) {
    var field = this.table.header[j];
    var background = this.getFieldBackground(field);
    rowBackgrounds.push(background)
  }
  var lineRange = this.getLineRange();
  lineRange.setBackgrounds([rowBackgrounds]);
};


/**
 * Commit a single item field in spreadsheet if the items order has not been changed since instantiating the grid.
 * @param {String} field: the field of the item to commit in spreadsheet.
 */
Item.prototype.commitField = function (field) {
  if (!(this.authorizedToCommit)) {
    throw "Forbidden to commit this item field. The order of the grid it is associated to has changed or it has been deleted."
  }
  var cellRange = this.getFieldRange(field);
  if (this.getFieldFormula(field)) {
    cellRange.setValue(this.getFieldFormula(field));  
  } else {
    cellRange.setValue(this.getFieldValue(field));
  }

  cellRange.setNote(this.getFieldNote(field));
  cellRange.setBackground(this.getFieldBackground(field));
  cellRange.setWrap(false);
  cellRange.setFontColor(this.getFieldFontColor(field));
};


/**
 * Commit a single item field value in spreadsheet if the items order has not been changed since instantiating the grid.
 * @param {String} field: the field of the item to commit the value from, in spreadsheet.
 */
Item.prototype.commitFieldValue = function (field) {
  if (!(this.authorizedToCommit)) {
    throw "Forbidden to commit this item field. The order of the grid it is associated to has changed or it has been deleted."
  }
  var cellRange = this.getFieldRange(field);
  if (this.getFieldFormula(field)) {
    cellRange.setValue(this.getFieldFormula(field));
  } else {
    cellRange.setValue(this.getFieldValue(field)); 
  }
};


/**
 * Get the line range of the item in the spreadsheet it is from.
 * @return {Range} line: The line range.
 */
Item.prototype.getLineRange = function () {
  var headerOffset = 1;
  var rangePositionOffset = this.table.row;
  var row = this.i + headerOffset + rangePositionOffset;
  var column = this.table.column;
  var sheet = this.table.sheet;
  return sheet.getRange(row, column, 1, this.table.header.length);
};


/**
 * Get the cell range of a given field of the item.
 * @param {string} field: A field string.
 * @return {Number} line: The line number.
 */
Item.prototype.getFieldRange = function (field) {
  var columnIndexOffset = 1;    // columns starts at 1.
  var columnField = this.table.header.indexOf(field) + columnIndexOffset;
  return this.getLineRange().getCell(1, columnField);
};



/**
 * Method to get the value of a given field.
 * @param {String} field: The name of the field.
 */
Item.prototype.getFieldValue = function(field) {
  var fieldParams = this.fields[field];
  if(!fieldParams) {
    var error = "The field '" + field + 
        "' cannot be found in the Table located in '"+ this.table.sheet.getSheetName() +
        "' sheet.\nCheck if the field exists, it's properly written and it's included in the Table range.";
    throw error;  
  }
  return fieldParams["value"];
};


/**
 * Method to set a value for a given field.
 * @param {String} field: The name of the field.
 * @param {String|Number|Date} value: The value to set.
 */
Item.prototype.setFieldValue = function(field, value) {
  if(!this.fields[field]) {    
    var error = "The field '" + field + 
        "' cannot be found in the Table located in '"+ this.table.sheet.getSheetName() +
        "' sheet.\nCheck if the field exists, it's properly written and it's included in the Table range.";
    throw error;
  }
  this.fields[field]["value"] = value;
  this.fields[field]["formula"] = '';
  return this;
};


/**
 * Method to get note for a given field.
 * @param {String} field: The name of the field.
 */
Item.prototype.getFieldNote = function(field) {
  return this.fields[field]["note"];
};


/**
 * Method to set note for a given field.
 * @param {String} field: The name of the field.
 * @param {String} note: The note to set.
 */
Item.prototype.setFieldNote = function(field, note) {
  this.fields[field]["note"] = note;
  return this
};


/**
 * Method to get background for a given field.
 * @param {String} field: The name of the field.
 */
Item.prototype.getFieldBackground = function(field) {
  return this.fields[field]["background"];
};


/**
 * Method to set background for a given field.
 * @param {String} field: The name of the field.
 * @param {String} background: The background to set (color string or hex code string).
 */
Item.prototype.setFieldBackground = function(field, background) {
  this.fields[field]["background"] = background;
  return this;
};


/**
 * Method to set background on the whole item.
 * @param {String} color: The name or hex of the color.
 */
Item.prototype.setBackground = function(color) {
  for (var i = 0; i < this.table.header.length; i++) {
    var field = this.table.header[i];
    this.fields[field]["background"] = color;
  }
  return this;
};


/**
 * Method to set font color on the whole item.
 * @param {String} color: The name or hex of the color.
 */
Item.prototype.setFontColor = function(color) {
  for (var i = 0; i < this.table.header.length; i++) {
    var field = this.table.header[i];
    this.fields[field]["font"] = color;
  }
  return this;
};


/**
 * Method to get formula for a given field.
 * @param {String} field: The name of the field.
 */
Item.prototype.getFieldFormula = function(field) {
  return this.fields[field]["formula"]
};

/**
 * Method to set formula for a given field.
 * @param {String} field: The name of the field.
 * @param {String} formula: The formula to set (must start with "=").
 */
Item.prototype.setFieldFormula = function(field, formula) {
  this.fields[field]["formula"] = formula;
  return this;
};


/**
 * Method to get font color for a given field.
 * @param {String} field: The name of the field.
 */
Item.prototype.getFieldFontColor = function(field) {
  return this.fields[field]["font"];
};


/**
 * Method to set font color for a given field.
 * @param {String} field: The name of the field.
 * @param {String} fontColor: The font color to set.
 */
Item.prototype.setFieldFontColor = function(field, fontColor) {
  this.fields[field]["font"] = fontColor;
  return this;
};


/**
 * Method to get the cell range for a specific field.
 * @param {String} field: The name of the field.
 * @return {Range} the cell range of the field.
 */
Item.prototype.getFieldRange = function(field) {
  var fieldIndex = this.table.header.indexOf(field);
  return this.getLineRange().getCell(1, fieldIndex + 1);
};

rowsData.gs

// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      // use 0 as value if it is present.
      values.push(header.length > 0 && (objects[i][header] || objects[i][header] == 0) ? objects[i][header] : "");
    }
    data.push(values);
  }

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}
// 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
//       This argument is optional and it defaults to all the cells except those in the first row
//       or all the cells below columnHeadersRowIndex (if defined).
//   - 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) {
  var headersIndex = columnHeadersRowIndex //|| range ? range.getRowIndex() - 1 : 1;
  var dataRange = range || 
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.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. 
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  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)) { // I removed this because result identifiers have '_' in name
    //  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';
}

selector.gs

/**
 * Constructor which creates a Selector object to query Items in a Table.
 * @param {Table} table: The Table object where to evaluate the criteria.
 * @param {Array} criteria: an array used as filter as an AND of ORs (see CNF). Examples:
 * >>> [{date: today}, [{tag: 1},{tag: 2}]] // (date === today && (tags === 1 || tags === 2))
 * >>> [[{assigneeId: 'GO'}, {assigneeId: 'AM'}]] // (assigneeId === 'GO' || assigneeId === 'AM')
 * >>> [{name: 'Guillem'}, {surname: 'Orpinell'}] // (name === 'Guillem' && surname === 'Orpinell')
 * >>> {name: 'Guillem', surname: 'Orpinell'} // (name === 'Guillem' && surname === 'Orpinell')
 * @constructor
 */
function Selector(table, criteria) {
  this.table = table;
  this.criteria = criteria;
  this.queryItems = new GridArray();
}


/**
 * Method to get the query items in a Selector object.
 */
Selector.prototype.getQueryItems = function() {
  return this.queryItems;
}


/**
 * Method to evaluate a criteria within a Table object.
 */
Selector.prototype.evaluate = function() {
  if (Array.isArray(this.criteria)) {
    var andsArray = this.criteria;
  }
  else if (isObject(this.criteria)) {
    var andsArray = [this.criteria];
  } else {
    throw 'Oops! Criteria should be an Array or an Object. Fix it and try again.'
  }
  
  for (var i = 0; i < this.table.items.length; i++) {
    var item = this.table.items[i];
    if (isMatching(item, andsArray)) {
      this.queryItems.push(item);
    }
  }
  return this
}


/**
 * Function to evaluate a criteria within an Item object.
 * @param {Item} item: The Item object where to evaluate the criteria.
 * @param {Array} criteria: an array used as filter as an AND of ORs (see CNF).
 @return {Boolean}
 */
function isMatching(item, andsArray) {  
  for (var i=0; i < andsArray.length; i++) {
    var clause = andsArray[i];
    if (isObject(clause) && someUnmatch(item, clause)) { //AND logic
      return false;
    }
    else if (Array.isArray(clause) && noneMatches(item, clause)) { //OR logic
      return false;
    }
  }
  return true;
}

/**
 * Function
 */
function someUnmatch(item, object) {
  for (var field in object) {
    if (!valuesMatch(object[field], item.getFieldValue(field))) {
      return true;
    }
  }
  return false;
}

/**
 * Function
 */
function noneMatches(item, orsArray) {
  for (var i=0; i < orsArray.length; i++) {
    var object = orsArray[i];
    if (!isObject(object)) {
      throw 'Oops! The ORs array must be an array of Objects. Fix it and try again.'
    }
    for (var field in object) {
      if (valuesMatch(object[field], item.getFieldValue(field))) {
        return false;
      }
    }
  }
  return true;
}
    
/**
 * Function to check a matching between two values, considering also value as a Date.
 */
function valuesMatch(value1, value2) {
  return ((value1 instanceof Date && value1.getTime() === value2.getTime()) || value1 === value2)
}

/** 
 * Returns if a value is an object
 */
function isObject (value) {
  return value && typeof value === 'object' && value.constructor.name === 'Object';
}

table.gs

// https://github.com/socialpoint-labs/sheetfu-apps-script

/**
 * Function to create a Table Object for a whole sheet
 * @param {string} sheetName: Name of the sheet to create a Table from
 * @param {number} headerRow: Row number where the header is.
 * @param {String} indexField: Field name you want to create an index with (commonly for ID field for fast lookup).
 * @returns {Table}
 */
function getTable(sheetName, headerRow, indexField) {
  if (!headerRow) {
    headerRow = 1;
  }
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var numberOfRows = sheet.getLastRow() - headerRow + 1;
  var tableRange = sheet.getRange(headerRow, 1, numberOfRows, sheet.getLastColumn());
  return new Table(tableRange, indexField);
}


/**
 * Function to create a Table Object from a Named Range. The range should contain a header in the first row.
 * Named ranges are ranges that have associated string aliases.
 * They can be viewed and edited via the Sheets UI under the Data > Named ranges... menu.
 * @param {string} namedRange: Name of the range to create a Table from
 * @param {String} indexField: Field name you want to create an index with (commonly for ID field for fast lookup).
 * @returns {Table}
 */
function getTableByName(namedRange, indexField) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tableRange = ss.getRangeByName(namedRange);
  if (tableRange == null) {
    throw 'Oops! Error creating a table with the named range '+namedRange+'. It might not exist or it is misspelled.'
  }
  return new Table(tableRange, indexField);
}


/** Constructor which create a Table object to query data, get and post. Object to use when rows in sheet are not uniquely
 * identifiable (no id). Use Table Class for DB-like queries instead (when unique id exist for each row).
 * @param {Range} gridRange: a range object from Google spreadsheet. First row of range must be the headers.
 * @param {String} indexField: Field name you want to create an index with (commonly for ID field for fast lookup).
 * @constructor
 */
function Table(gridRange, indexField) {

  this.gridRange = trimRangeRows(gridRange);
  this.initialGridRange = this.gridRange;
  this.header = this.getHeader();
  this.items = this.initiateItems();

  this.indexField = indexField;
  if (this.indexField !== undefined) {
    this.index = this.getIndex(indexField);
  }
}


/**
 * Function to trim the rows of a range. The range should contain a header in the first row.
 * @param {Range} range: a range object from Google spreadsheet. First row of range must be the headers.
 * @returns {Range}
 */
function trimRangeRows(range) {
  var values = range.getValues();
  for (var rowIndex = values.length - 1; rowIndex >= 0; rowIndex--) {
    if (values[rowIndex].join('') !== '') {
      break;
    }
  }
  return range.offset(rowOffset=0, columnOffset=0, numRows=rowIndex+1);
}


/**
 * Method to extract headers of a grid.
 * @return {Array} The list of labels.
 */
Table.prototype.getHeader = function () {
  return this.gridRange.getValues()[0];
};


/**
 * Method to create an index as a hash table for a given field. Make sure the field guarantees unique values. Perfect for IDs.
 * @return {Object} Hash table in the format {fieldIndex : TableItem}
 */
Table.prototype.getIndex = function (indexField) {
  var index = {};
  for (var i = 0; i < this.items.length; i++) {
    var key = this.items[i].getFieldValue(indexField);
    index[key] = this.items[i];
  }
  return index;
};


/**
 * Method to extract data from the grid range as Item objects (using header labels).
 * @return {Item[]} List of Item objects.
 * The i attribute is the index of the object in the list of Table.items (starting at 0) (not the line in spreadsheet).
 */
Table.prototype.initiateItems = function() {
  var rawValues = this.gridRange.getValues().slice(1);    // we disregard first row because it is header.
  var rawNotes = this.gridRange.getNotes().slice(1);
  var rawBackgrounds = this.gridRange.getBackgrounds().slice(1);
  var rawFormulas = this.gridRange.getFormulasR1C1().slice(1);
  var rawFontColors = this.gridRange.getFontColors().slice(1);

  var items = new GridArray();

  for (var row = 0; row < rawValues.length; row++) {
    var parseItem = new Item(row, this.header, this.gridRange.getRow(), this.gridRange.getColumn(), this.gridRange.getSheet());
    for (var column = 0; column < this.header.length; column++) {
      var label = this.header[column];
      parseItem.addField(
        label=label,
        value=rawValues[row][column],
        note=rawNotes[row][column],
        background=rawBackgrounds[row][column],
        formula=rawFormulas[row][column],
        font=rawFontColors[row][column]
      )
    }
    items.push(parseItem);
  }
  return items;
};


/**
 * Method to commit the items into the associated sheet (regardless if number of items have changed).
 */
Table.prototype.commit = function() {
  var dataToSend = this.getGridData();
  var itemsRange = this.getItemsRange();
  this.cleanInitialGrid();
  this.initialGridRange = this.gridRange;
  if(itemsRange !== undefined) {
    itemsRange.setValues(dataToSend['values']);
    itemsRange.setNotes(dataToSend['notes']);
    itemsRange.setBackgrounds(dataToSend['backgrounds']);
    itemsRange.setWraps(dataToSend['wraps']);
    itemsRange.setFontColors(dataToSend['fonts']);
  }
};


/**
 * Method to commit the items values into the associated sheet (regardless if number of items have changed).
 */
Table.prototype.commitValues = function() {
  var values = this.getGridValues();
  var itemsRange = this.getItemsRange();
  this.cleanInitialGrid();
  this.initialGridRange = this.gridRange;
  if(itemsRange !== undefined) {
    itemsRange.setValues(values);
  }
};

/**
 * Method to get the new Range for the items, based on lenght of Table.items.
 * @return {Range} object of the items range. {Undefined} if the items range is empty.
 */
Table.prototype.getItemsRange = function() {
  // We need to check that items is not empty, since Sheet.getRange() throws an exception if numRows or numColumns are 0.
  if(this.items.length === 0) {
    return undefined;
  }
  var row = this.gridRange.getRow() + 1;    // +1 to disregard header row
  var column = this.gridRange.getColumn();
  var sheet = this.gridRange.getSheet();
  return sheet.getRange(row, column, this.items.length, this.header.length);
};

/**
 * Method to create both values and notes 2D arrays from grid items.
 * @return {object} with attributes "values" and "notes".
 */
Table.prototype.getGridData = function() {
  var values = [];
  var notes = [];
  var backgrounds = [];
  var wraps = [];
  var fontColors =[];

  for (var i = 0; i < this.items.length; i++) {
    var rowValues = [];
    var rowNotes = [];
    var rowBackgrounds = [];
    var rowWraps = [];
    var rowFontColors = [];
    var item = this.items[i];

    for (var j = 0; j < this.header.length; j++) {
      var field = this.header[j];
      var value = item.getFieldValue(field);
      var formula = item.getFieldFormula(field);

      (formula)? rowValues.push(formula) : rowValues.push(value);
      rowNotes.push(item.getFieldNote(field));
      rowBackgrounds.push(item.getFieldBackground(field));
      rowWraps.push(false);
      rowFontColors.push(item.getFieldFontColor(field))
    }
    values.push(rowValues);
    notes.push(rowNotes);
    backgrounds.push(rowBackgrounds);
    wraps.push(rowWraps);
    fontColors.push(rowFontColors)
  }
  return {"values": values, "notes": notes, "backgrounds": backgrounds, "wraps": wraps, "fonts": fontColors}
};


/**
 * Method to create 2D array of the values of every grid items.
 * @return {Array[]} The values 2D array.
 */
Table.prototype.getGridValues = function() {
  var values = [];

  for (var i = 0; i < this.items.length; i++) {
    var rowValues = [];
    var item = this.items[i];

    for (var j = 0; j < this.header.length; j++) {
      var field = this.header[j];
      var value = item.getFieldValue(field);
      var formula = item.getFieldFormula(field);

      (formula)? rowValues.push(formula) : rowValues.push(value);
    }
    values.push(rowValues);
  }
  return values
};

/**
 * Method to query rows from a Table, given exact match attributes.
 * @param {Array} criteria: an array used as filter as an AND of ORs (see CNF).
 * @return {Item[]} List of Item objects matching the given criteria.
 */
Table.prototype.select = function(criteria) {
  var queryItems = new Selector(this, criteria)
  .evaluate()
  .getQueryItems();
  
  return queryItems;
};


/**
 * Method to update one item within items grid.
 * @param {object} item: an item from items.
 * The index value is the value where the item is in the Table.items array. Needed to be able to change the value in Table.
 */
Table.prototype.update = function(item) {
  this.items[item['_i']] = item;
};


/**
 * Method to update many items within items grid.
 * @param {object[]} manyItems: list of objects to update.
 */
Table.prototype.updateMany = function(manyItems) {
  for (var i = 0; i < this.items.length; i++) {
    var index = manyItems[i]['_i'];
    this.items[index] = manyItems[i];
  }
};



/**
 * Method to delete items from the items grid based on a selection criteria.
 * @param {object} filteredObject: Criteria to select the items to delete. See documentation of the "select" method.
 */
Table.prototype.deleteSelection = function(filterObject) {
  var selectionToDelete = this.select(filterObject);
  return this.deleteMany(selectionToDelete);
};

/**
 * Method to delete several items from the items grid.
 * @param {list} itemList: A list of items that you wish to delete
 * Take into account that deleting items re-calculates the indices of all items with higher index inside Table.items.
 */
Table.prototype.deleteMany = function(itemList) {
  if(itemList.length === this.items.length)
  {
    return this.deleteAll();
  }
  
  // First we sort the list of items to delete by index
  itemList.sort(function(firstItem, secondItem) {
    // Compare the i attribute of both items
    if(firstItem.i < secondItem.i) return -1;
    if(firstItem.i > secondItem.i) return 1;
    return 0;
  });
  
  // Now we iterate the sorted list in inverse order and delete the items
  var indexReduction = itemList.length;
  var lastDeletedIndex = this.items.length - 1;
  for(var i = itemList.length - 1; i >= 0; i--)
  {
    var itemToDelete = itemList[i];
    itemToDelete.authorizedToCommit = false; // To prevent the user from commiting deleted items.
    var indexToDelete = itemToDelete.i;
    if(indexToDelete >= this.items.length) {
      throw "One of the items specified to delete has an out of bounds index.";
    }
    this.items.splice(indexToDelete, 1);
    
    // For every item to delete, we will recalculate the indexes from the item that was deleted
    // to the last item before the previously deleted index.
    for (var k = indexToDelete; k < lastDeletedIndex - 1; k++) {
      var itemToUpdateIndex = this.items[k];
      // We reduce the index by as many items are left to delete
      itemToUpdateIndex.i = itemToUpdateIndex.i - indexReduction;
    }
    lastDeletedIndex = indexToDelete;
    indexReduction--;
  }
  
  // Reduce the gridRange by as many rows as were deleted
  this.gridRange = this.gridRange.offset(0, 0, this.gridRange.getHeight() - itemList.length, this.gridRange.getWidth());
};


/**
 * Method to delete one item from the items grid.
 * @param {item} item: An item from this.items that you wish to delete
 * Take into account that deleting an item re-calculates the indices of all items with higher index inside Table.items.
 */
Table.prototype.deleteOne = function(item) {
  return this.deleteMany([item]);
};


/**
 * Method to delete all items withing the items grid.
 */
Table.prototype.deleteAll = function() {
  this.items = new GridArray();
  this.gridRange = this.getHeaderRange();
};


/**
 * Method to delete all rows inside the initial grid.
 */
Table.prototype.cleanInitialGrid = function() {
  this.initialGridRange.clear({contentsOnly: true, skipFilteredRows: true});
  var header = this.getHeaderRange();
  header.setValues([this.header]);
};


/**
 * Method to get the range of the header.
 * @return {Range} headerRange: the range of the header only (basically top row).
 */
Table.prototype.getHeaderRange = function() {
  var row = this.gridRange.getRow();
  var column = this.gridRange.getColumn();
  var sheet = this.gridRange.getSheet();
  return sheet.getRange(row, column, 1, this.header.length)
};


/**
 * Method to add a new item into the Table. Add the item also to index if there is an index.
 * @param {object} input_item: an object item containing only values, or an instance of Item. Field must be matching header values.
 */
Table.prototype.add = function(input_item) { 
  
  var raw_item = input_item;
  if(input_item instanceof Item) {
    raw_item = {}
    for (var field in input_item.fields) { 
      raw_item[field] = input_item.getFieldValue(field); 
    }
  }
  
  var newItem = new Item(this.items.length, this.header, this.gridRange.getRow(), this.gridRange.getColumn(), this.gridRange.getSheet());

  for (var i = 0; i < this.header.length; i++) {
    var label = this.header[i];
    if (raw_item[label] === undefined) {
      raw_item[label] = "";
    }
    newItem.addField(field=label, value=raw_item[label]);
  }
  this.items.push(newItem);
  
  // Increase the gridRange by one row
  this.gridRange = this.gridRange.offset(0, 0, this.gridRange.getHeight()+1, this.gridRange.getWidth());
  
  if (this.index !== undefined) {
    var indexId = newItem.getFieldValue(this.indexField);
    this.index[indexId] = newItem;
  }
  return newItem;
};


/**
 * Method to sort Table.items for a given field/key/label value.
 * Only works for numbers and date fields.
 * @param {string} key: the key label that we need to sort items from.
 * @param {boolean} ascending: If True it sorts ascending, if false, it sort descending.
 * @return {object[]} items: Table.items attribute.
 */
Table.prototype.sortBy = function(key, ascending) {

  this.items.sort(function(a, b) {
    var timeStampA = Date.parse(a.getFieldValue(key));
    var timeStampB = Date.parse(b.getFieldValue(key));
    if (!isNaN(timeStampA) && !isNaN(timeStampB)) {
      var dateA = new Date(a.getFieldValue(key));
      var keyA = dateA.getTime();
      var dateB = new Date(b.getFieldValue(key));
      var keyB = dateB.getTime();
    } else {
      var keyA = a.getFieldValue(key);
      var keyB = b.getFieldValue(key);
    }

    // Compare the 2 keys
    if(keyA < keyB) return -1;
    if(keyA > keyB) return 1;
    return 0;

  });

  if (ascending === false) {
    this.items.reverse()
  }

  // updating '_i'
  for (var i = 0; i < this.items.length; i++) {
    this.items[i].i = i;
    this.items[i].authorizedToCommit = false;       // to prevent committing lines when order has changed.
  }
  return this.items
};


/**
 * Method to clear background colors on every items.
 * @return {Range}: The range of items which had their background cleaned. {Undefined} if the items range is empty.
 */
Table.prototype.clearBackgrounds = function () {
  var itemRange = this.getItemsRange();
  if(itemRange !== undefined) {
    return itemRange.clearFormat();
  }
  else {
    return undefined;
  }
};


/**
 * Get an item from the table by its ID (assuming an index field was given when creating the table).
 */
Table.prototype.getItemById = function (valueId) {
  return this.index[valueId]
};
 

/**  
 * Vertical lookup. Searches down the index field of a table (assuming an index field was given when creating the table)
 * for a criteria and returns the value of a specified field in the item found.
 */
Table.prototype.getFieldValueById = function (field, valueId) {
  var itemById = this.getItemById(valueId);
  if(itemById) {
    return itemById.getFieldValue(field);
  } else {
    return undefined;
  }
}


/**
 * Method to return only distinct different values in a field.
 */
Table.prototype.distinct = function(field) {
  var list = [];
  for (var i = 0; i < this.items.length; i++) {
    list.push(this.items[i].getFieldValue(field));
  }
  // It filters the list to return an array with the unique values
  var unique = list.filter(function(value, index, self) { 
    return self.indexOf(value) === index;
  });
  return unique
}


/**
 * Function to clone an object and simulate inheritance.
 */
function cloneObj(obj) {
  function F() { }
  F.prototype = obj;
  return new F();
}


/**
 * SubArray class constructor to have more ORM like methods to the arrays used in the Table class.
 */
function GridArray() {}
GridArray.prototype = cloneObj(Array.prototype);


/**
 * Method to return only the first result of an array. Useful when result of selection.
 */
GridArray.prototype.first = function() {
  if (this.length === 0) {
    return undefined;
  }
  return this[0];
};


/**
 * Method to return the first x results of an array. Useful when result of selection.
 */
GridArray.prototype.limit = function(x) {
  if(this.length > x) {
    return this.slice(0, x);
  } else {
    return this;
  }
};