function _sheetnode_google_import_do in Sheetnode 7.2

Same name and namespace in other branches

1. 6 modules/sheetnode_google/sheetnode_google.import.inc \_sheetnode_google_import_do()
2. 7 modules/sheetnode_google/sheetnode_google.import.inc \_sheetnode_google_import_do()

1 call to _sheetnode_google_import_do()

_sheetnode_google_batch_import in modules/sheetnode_google/sheetnode_google.import.inc

File

modules/sheetnode_google/sheetnode_google.import.inc, line 230

Extension to sheetnode for importing from Google sheets.

Code

function _sheetnode_google_import_do($spreadsheetService, $spreadsheet, $worksheet) {
  module_load_include('inc', 'sheetnode', 'socialcalc');

  // SocialCalc array structure.
  $sc = array();
  try {
    $sc['attribs']['lastrow'] = $worksheet
      ->getRowCount()->text + 1;
    $sc['attribs']['lastcol'] = $worksheet
      ->getColumnCount()->text + 1;
    $worksheet_key = array_pop(explode('/', $worksheet->id->text));
    $spreadsheet_key = array_pop(explode('/', $spreadsheet->id->text));
    $row = 0;

    // TODO: Compute a number instead of choosing an arbitrary one.
    $page = 100;

    // We're paging because attempting to retrieve large sheets in one go
    // exhausts the memory.
    $cell = array();
    while ($row < $sc['attribs']['lastrow'] - 1) {
      $query = new Zend_Gdata_Spreadsheets_CellQuery();
      $query
        ->setSpreadsheetKey($spreadsheet_key);
      $query
        ->setWorksheetId($worksheet_key);
      $query
        ->setMinRow($row);
      $query
        ->setMaxRow(min($sc['attribs']['lastrow'] - 1, $row + $page));
      $feed = $spreadsheetService
        ->getCellFeed($query);
      foreach ($feed as $entry) {
        $cells[] = array(
          'col' => $entry
            ->getCell()
            ->getColumn(),
          'row' => $entry
            ->getCell()
            ->getRow(),
          'value' => $entry
            ->getCell()
            ->getInputValue(),
          'numeric' => $entry
            ->getCell()
            ->getNumericValue(),
          'content' => $entry->content->text,
        );
      }
      $row += $page;
    }
  } catch (Exception $e) {

    // TODO: Set the error in Batch API.
    watchdog('sheetnode_google', $e
      ->getMessage(), array(), WATCHDOG_ERROR);
    return '';
  }
  if ($cells) {
    foreach ($cells as $cell) {
      try {
        $c = array();
        $c['pos'] = array(
          $cell['col'],
          $cell['row'],
        );
        $value = $cell['value'];
        $numeric = $cell['numeric'];
        $content = $cell['content'];
        if ($value[0] == '=') {

          // Formula.
          $c['datatype'] = 'f';
          $c['valuetype'] = $numeric ? 'n' : 't';
          $c['datavalue'] = $content;

          // Import the Google formula, where cells are referenced as R[delta_row]C[delta_col].
          global $_sheetnode_google_import_col, $_sheetnode_google_import_row;
          $_sheetnode_google_import_col = $c['pos'][0];
          $_sheetnode_google_import_row = $c['pos'][1];
          $formula = preg_replace_callback('/R(?:(\\[)?(-?\\d+)\\]?)?C(?:(\\[)?(-?\\d+)\\]?)?/', '_sheetnode_google_import_formula_replace_coords', $value);
          $formula = str_replace(';', ',', $formula);
          $c['formula'] = substr($formula, 1);
          if ($numeric) {
            _sheetnode_google_import_infer_valueformat($sc, $c, $numeric, trim($content));
          }
        }
        elseif ($numeric) {

          // Number.
          $c['datatype'] = 'v';
          $c['valuetype'] = 'n';
          $c['datavalue'] = $numeric;
          _sheetnode_google_import_infer_valueformat($sc, $c, $numeric, trim($value));
        }
        else {

          // text, probably :-).
          $html = _filter_url($value, NULL);
          if ($html != $value) {
            $c['valuetype'] = 'th';
            $c['datavalue'] = $html;
          }
          else {
            $c['valuetype'] = 't';
            $c['datavalue'] = $value;
          }
          $c['datatype'] = 't';
        }
        $sc['cells'][socialcalc_cr_to_coord($c['pos'][0], $c['pos'][1])] = $c;
      } catch (Exception $e) {

        // TODO: Set the error in Batch API.
        watchdog('sheetnode_google', $e
          ->getMessage(), array(), WATCHDOG_WARNING);
      }
    }
  }
  $socialcalc = array(
    'sheet' => $sc,
    'edit' => socialcalc_default_edit($sc),
    'audit' => socialcalc_default_audit($sc),
  );
  return socialcalc_save($socialcalc);
}