How can I prevent the leading zero from being removed when importing zip codes from a Google spreadsheet to a Google fusion table?

Question

I have a Google form in which users enter their zip code. The form responses are recorded onto a Google spreadsheet, which is then synced with a Google Fusion Table to be geocoded onto a map. The zip codes are formulated as text in the spreadsheet to retain the leading zero, but when synced to the fusion table the zero is removed. Is there a way to retain the column formatting when the spreadsheet is synced to fusion tables so the leading zero is not removed?

Answer 1

From those instructions, there is one function named sync(). See code below: So, I'm assuming that this is the code that is doing the work. This is to big for the comments section, so I'm posting it in an answer, or I'd have to edit your question. So, let me know in the comments if this is the code.

/**
 * 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));

  // 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);
    if (column === ADDRESS_COLUMN) {
      escapedColumns.push(escapedQuotes(LOCATION_COLUMN));
    }
  }

  // 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, true);
        }
      }

      // 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();
  }
}

Answer 2

I have the same problem while importing account numbers. Whenever I am importing any account numbers, all the leading zero before numbers not showing.

I have solved it using data as plain text. Before importing any value from the spreadsheet, make column value plain text using Format Menu -> Number -> Plain Text