<commit_before>/**
 * AppsScript script to run in a Google Spreadsheet that synchronizes
 * Google Forms responses with a Fusion Table.
 */

var DOCID;
var ADDRESS_COLUMN;
var LOCATION_COLUMN;
var TIME_ZONE;
var STREET_ADDRESS_COLUMN;
var CITY_COLUMN;
var STATE_COLUMN;

/**
 * Do-nothing method to trigger the authorization dialog if not already done.
 */
function checkAuthorization() {
}

/**
 * Syncs the Fusion Table to the form data. Run this every hour or so.
 */
function sync() {
  init();

  // Get the data in the spreadsheet and convert it to a dictionary.
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var spreadsheetData = sheet.getRange(1, 1, lastRow, lastColumn);
  var spreadsheetValues = spreadsheetData.getValues();
  var columns = spreadsheetValues[0];
  var spreadsheetMap = mapRowsByRowId(columns,
      spreadsheetValues.slice(1, spreadsheetValues.length));
  
  var x = 0;
  for (var rowId in spreadsheetMap) {
    var spreadsheetRows = spreadsheetMap[rowId];
    for (var x in spreadsheetRows) {
      var spreadsheetRow = spreadsheetRows[ x ];
      /*
      if ( spreadsheetRow[ CITY_COLUMN ].length == 0 ) {
      spreadsheetRow[ CITY_COLUMN ] = 'Kansas City';
      }
      
      if (spreadsheetRow[ STATE_COLUMN ].length == 0 ) {
      spreadsheetRow[ STATE_COLUMN ] = 'MO';
      }
      */
      spreadsheetRow[ ADDRESS_COLUMN ] = spreadsheetRow[ STREET_ADDRESS_COLUMN ] + ', ' + spreadsheetRow[ CITY_COLUMN ] + ', ' + spreadsheetRow[ STATE_COLUMN ];
    }
  }

  // Get the columns in the spreadsheet and escape any single quotes
  var escapedColumns = [];
  for (var i = 0; i < columns.length; i++) {
    var columnName = columns[i];
    columnName = escapeQuotes(columnName);
    escapedColumns.push(columnName);
  }

  // Get the data from the table and convert to a dictionary.
  var query = "SELECT '" + escapedColumns.join("','") + "' FROM " + DOCID;
  var ftResults = runSqlWithRetry(query);
  if (!ftResults) {
    return;
  }
  var ftMap = mapRowsByRowId(ftResults.columns, ftResults.rows);

  // For each row in the Fusion Table, find if the row still exists in the
  // spreadsheet. If it exists, make sure the values are the same. If
  // they are different, update the Fusion Table data.
  // If the row doesn't exist in the spreadsheet, delete the row from the table.
  for (var rowId in ftMap) {
    var spreadsheetRow = spreadsheetMap[rowId];
    if (spreadsheetRow) {
      var updates = [];
      var tableRow = ftMap[rowId];

      for (var column in tableRow) {
        if (column === 'rowid') {
          continue;
        }
        var tableValue = tableRow[column];
        var spreadsheetValue = spreadsheetRow[column];
        if (tableValue != spreadsheetValue) {
          spreadsheetValue = processSpreadsheetValue(column, spreadsheetValue);
          updates.push("'" + escapeQuotes(column) + "' = '" +
              spreadsheetValue + "'");
        }
      }

      // If there are updates, send the UPDATE query.
      if (updates.length) {
        var query = [];
        query.push('UPDATE ');
        query.push(DOCID);
        query.push(' SET ');
        query.push(updates.join(','));
        query.push(" WHERE rowid = '");
        query.push(rowId);
        query.push("'");
        runSqlWithRetry(query.join(''));
        waitBetweenCalls();
      }

    } else {
      // If the row doesn't exist in the spreadsheet, delete it from the table
      runSqlWithRetry('DELETE FROM ' + DOCID + " WHERE rowid = '" +
          rowId + "'");
      waitBetweenCalls();
    }
  }

  // Insert all the data into the Fusion Table that failed to insert.
  // These rows were given a rowid of -1 or have a blank rowid.
  var failedInserts = spreadsheetMap[-1];
  for (var i = 0; failedInserts && i < failedInserts.length; i++) {
    var rowId = createRecord(failedInserts[i]);
    if (!rowId) {
      rowId = -1;
    }
    insertRowId(rowId, failedInserts[i].spreadsheetRowNum);
    waitBetweenCalls();
  }
}

/**
 * Submits the data to Fusion Tables when the form is submitted.
 * @param {Object} e The form object.
 */
function onFormSubmit(e) {
  if (!e) {
    // Don't try to do anything when run directly from the script editor
    return;
  }
  // Get the row number of the newly entered data.
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getLastRow();

  // Check to make sure the rowid column is there.
  init();

  // The values entered into the form, mapped by question.
  var formValues = e.namedValues;
  
  Logger.log('Form values are ' + formValues);

  // Insert the data into the Fusion Table.
  var rowId = createRecord(formValues);
  if (!rowId) {
    rowId = -1;
  }
  insertRowId(rowId, row);
}

/**
 * Ensures the docid property for the target table is set, that if the
 * address column is set the latlngColumn is also set, and adds a rowid
 * column to the sheet if it doesn't have one.
 */
function init() {

// Update for deprecated ScriptProperties API call

  var docProperties = PropertiesService.getScriptProperties();  
  DOCID = docProperties.getProperty('docid');
  
  Logger.log(DOCID)
  
  // Here is the first of two modifications to make the Address and Location work -- lc
  
  if (!DOCID) {
    throw 'The script is missing the required docid Project Property';
  }
  ADDRESS_COLUMN = docProperties.getProperty('addressColumn');
  Logger.log('Address Column is ' + ADDRESS_COLUMN);
  
  LOCATION_COLUMN = docProperties.getProperty('latlngColumn');
  Logger.log ('latlngColumn is ' + LOCATION_COLUMN);
              
  STREET_ADDRESS_COLUMN = docProperties.getProperty('streetAddressColumn');  
  Logger.log ('streetstreetAddressColumnAddress is ' + STREET_ADDRESS_COLUMN);
              
  CITY_COLUMN = docProperties.getProperty('cityColumn');
  Logger.log ('cityColumn is ' + CITY_COLUMN);
              
  STATE_COLUMN = docProperties.getProperty('stateColumn');
  Logger.log ('stateColumn is ' + STATE_COLUMN);
              
  if (ADDRESS_COLUMN && !LOCATION_COLUMN) {
    throw('Since you added an ADDRESS_COLUMN project property, ' +
        'you also need to add a latlngColumn property');
  }
  TIME_ZONE = Session.getScriptTimeZone();
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var lastHeaderValue = sheet.getRange(1, lastColumn).getValue();
  if (lastHeaderValue != 'rowid') {
    sheet.getRange(1, lastColumn + 1).setValue('rowid');
  }
}

/**
 * Creates a record in the Fusion Table.
 * @param {Object} dictionary of columns mapped to values.
 * @return {?string} the rowid if successful, otherwise null.
 */
function createRecord(columnValues) {<commit_msg>Added ZIP to sync() in script<commit_after>/**
 * AppsScript script to run in a Google Spreadsheet that synchronizes
 * Google Forms responses with a Fusion Table.
 */

var DOCID;
var ADDRESS_COLUMN;
var LOCATION_COLUMN;
var TIME_ZONE;
var STREET_ADDRESS_COLUMN;
var CITY_COLUMN;
var STATE_COLUMN;
var ZIP_COLUMN;

/**
 * Do-nothing method to trigger the authorization dialog if not already done.
 */
function checkAuthorization() {
}

/**
 * Syncs the Fusion Table to the form data. Run this every hour or so.
 */
function sync() {
  init();

  // Get the data in the spreadsheet and convert it to a dictionary.
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var spreadsheetData = sheet.getRange(1, 1, lastRow, lastColumn);
  var spreadsheetValues = spreadsheetData.getValues();
  var columns = spreadsheetValues[0];
  var spreadsheetMap = mapRowsByRowId(columns,
      spreadsheetValues.slice(1, spreadsheetValues.length));
  
  var x = 0;
  for (var rowId in spreadsheetMap) {
    var spreadsheetRows = spreadsheetMap[rowId];
    for (var x in spreadsheetRows) {
      var spreadsheetRow = spreadsheetRows[ x ];
      /*
      if ( spreadsheetRow[ CITY_COLUMN ].length == 0 ) {
      spreadsheetRow[ CITY_COLUMN ] = 'Kansas City';
      }
      
      if (spreadsheetRow[ STATE_COLUMN ].length == 0 ) {
      spreadsheetRow[ STATE_COLUMN ] = 'MO';
      }
      */
      spreadsheetRow[ ADDRESS_COLUMN ] = spreadsheetRow[ STREET_ADDRESS_COLUMN ] + ', ' + spreadsheetRow[ CITY_COLUMN ] + ', ' + spreadsheetRow[ STATE_COLUMN ] + ' ' + spreadsheetRow[ ZIP_COLUMN ];
    }
  }

  // Get the columns in the spreadsheet and escape any single quotes
  var escapedColumns = [];
  for (var i = 0; i < columns.length; i++) {
    var columnName = columns[i];
    columnName = escapeQuotes(columnName);
    escapedColumns.push(columnName);
  }

  // Get the data from the table and convert to a dictionary.
  var query = "SELECT '" + escapedColumns.join("','") + "' FROM " + DOCID;
  var ftResults = runSqlWithRetry(query);
  if (!ftResults) {
    return;
  }
  var ftMap = mapRowsByRowId(ftResults.columns, ftResults.rows);

  // For each row in the Fusion Table, find if the row still exists in the
  // spreadsheet. If it exists, make sure the values are the same. If
  // they are different, update the Fusion Table data.
  // If the row doesn't exist in the spreadsheet, delete the row from the table.
  for (var rowId in ftMap) {
    var spreadsheetRow = spreadsheetMap[rowId];
    if (spreadsheetRow) {
      var updates = [];
      var tableRow = ftMap[rowId];

      for (var column in tableRow) {
        if (column === 'rowid') {
          continue;
        }
        var tableValue = tableRow[column];
        var spreadsheetValue = spreadsheetRow[column];
        if (tableValue != spreadsheetValue) {
          spreadsheetValue = processSpreadsheetValue(column, spreadsheetValue);
          updates.push("'" + escapeQuotes(column) + "' = '" +
              spreadsheetValue + "'");
        }
      }

      // If there are updates, send the UPDATE query.
      if (updates.length) {
        var query = [];
        query.push('UPDATE ');
        query.push(DOCID);
        query.push(' SET ');
        query.push(updates.join(','));
        query.push(" WHERE rowid = '");
        query.push(rowId);
        query.push("'");
        runSqlWithRetry(query.join(''));
        waitBetweenCalls();
      }

    } else {
      // If the row doesn't exist in the spreadsheet, delete it from the table
      runSqlWithRetry('DELETE FROM ' + DOCID + " WHERE rowid = '" +
          rowId + "'");
      waitBetweenCalls();
    }
  }

  // Insert all the data into the Fusion Table that failed to insert.
  // These rows were given a rowid of -1 or have a blank rowid.
  var failedInserts = spreadsheetMap[-1];
  for (var i = 0; failedInserts && i < failedInserts.length; i++) {
    var rowId = createRecord(failedInserts[i]);
    if (!rowId) {
      rowId = -1;
    }
    insertRowId(rowId, failedInserts[i].spreadsheetRowNum);
    waitBetweenCalls();
  }
}

/**
 * Submits the data to Fusion Tables when the form is submitted.
 * @param {Object} e The form object.
 */
function onFormSubmit(e) {
  if (!e) {
    // Don't try to do anything when run directly from the script editor
    return;
  }
  // Get the row number of the newly entered data.
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = sheet.getLastRow();

  // Check to make sure the rowid column is there.
  init();

  // The values entered into the form, mapped by question.
  var formValues = e.namedValues;
  
  Logger.log('Form values are ' + formValues);

  // Insert the data into the Fusion Table.
  var rowId = createRecord(formValues);
  if (!rowId) {
    rowId = -1;
  }
  insertRowId(rowId, row);
}

/**
 * Ensures the docid property for the target table is set, that if the
 * address column is set the latlngColumn is also set, and adds a rowid
 * column to the sheet if it doesn't have one.
 */
function init() {

// Update for deprecated ScriptProperties API call

  var docProperties = PropertiesService.getScriptProperties();  
  DOCID = docProperties.getProperty('docid');
  
  Logger.log(DOCID)
  
  // Here is the first of two modifications to make the Address and Location work -- lc
  
  if (!DOCID) {
    throw 'The script is missing the required docid Project Property';
  }
  ADDRESS_COLUMN = docProperties.getProperty('addressColumn');
  Logger.log('Address Column is ' + ADDRESS_COLUMN);
  
  LOCATION_COLUMN = docProperties.getProperty('latlngColumn');
  Logger.log ('latlngColumn is ' + LOCATION_COLUMN);
              
  STREET_ADDRESS_COLUMN = docProperties.getProperty('streetAddressColumn');  
  Logger.log ('streetstreetAddressColumnAddress is ' + STREET_ADDRESS_COLUMN);
              
  CITY_COLUMN = docProperties.getProperty('cityColumn');
  Logger.log ('cityColumn is ' + CITY_COLUMN);
              
  STATE_COLUMN = docProperties.getProperty('stateColumn');
  Logger.log ('stateColumn is ' + STATE_COLUMN);
              
  ZIP_COLUMN = docProperties.getProperty('zipColumn');
  Logger.log ('zipColumn is ' + ZIP_COLUMN);
              
  if (ADDRESS_COLUMN && !LOCATION_COLUMN) {
    throw('Since you added an ADDRESS_COLUMN project property, ' +
        'you also need to add a latlngColumn property');
  }
  TIME_ZONE = Session.getScriptTimeZone();
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var lastHeaderValue = sheet.getRange(1, lastColumn).getValue();
  if (lastHeaderValue != 'rowid') {
    sheet.getRange(1, lastColumn + 1).setValue('rowid');
  }
}

/**
 * Creates a record in the Fusion Table.
 * @param {Object} dictionary of columns mapped to values.
 * @return {?string} the rowid if successful, otherwise null.
 */
function createRecord(columnValues) {
{'fraction_non_alphanumeric': 0.10987962419260129, 'fraction_numerical': 0.002348796241926013, 'mean_word_length': 3.0562667460553734, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3531736', 'n_tokens_mistral': 4191, 'n_tokens_neox': 4070, 'n_words': 1285}