class GoogleDrive::Worksheet
Public Instance Methods
add_headers(headers)
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 20 def add_headers(headers) headers.each_with_index do |h,h_i| self[1,h_i+1] = h end self.save end
add_or_update_rows(upd_rows)
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 35 def add_or_update_rows(upd_rows) sheet = self curr_rows = sheet.to_tsv.tsv_to_hash_array headers = curr_rows.first.keys curr_rows = [] if curr_rows.length==1 and curr_rows.first['name'].nil? curr_row_names = curr_rows.map{|r| r['name']} upd_rows.each_with_index do |row,urow_i| crow_i = curr_row_names.index(row['name']) if crow_i.nil? curr_row_names << row['name'] crow_i = curr_row_names.length-1 end row.each do |col_n,col_v| col_v_i = headers.index(col_n) sheet[crow_i+2,col_v_i+1] = col_v end end sheet.save end
check_and_fix(tsv)
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 151 def check_and_fix(tsv) sheet = self sheet.reload #loading remote data for checksum rem_tsv = sheet.to_tsv return true if rem_tsv.to_s.length==0 rem_table = rem_tsv.split("\n").map{|r| r.split("\t").map{|v| v.googlesafe}} loc_table = tsv.split("\n").map{|r| r.split("\t").map{|v| v.googlesafe}} re_col_vs = [] errcnt = 0 #checking cells loc_table.each_with_index do |loc_row,row_i| loc_row.each_with_index do |loc_v,col_i| rem_row = rem_table[row_i] if rem_row.nil? errcnt+=1 "No Row #{row_i} for Write Dst".oputs break else rem_v = rem_table[row_i][col_i] if loc_v != rem_v if ['true','false'].include?(loc_v.to_s.downcase) #google sheet upcases true and false. ignore elsif loc_v.to_s.downcase.gsub("-","").gsub(" ","")==rem_v.to_s.downcase.gsub("-","").gsub(" ","") #supported currency, silently converted whether it's an actual currency or not #put a backtick on it. sheet[row_i+1,col_i+1] = %{'#{loc_v}} re_col_vs << {'row_i'=>row_i+1,'col_i'=>col_i+1,'col_v'=>%{'#{loc_v}}} elsif (loc_v.to_s.count('e')==1 or loc_v.to_s.count('e')==0) and loc_v.to_s.sub('e','').to_i.to_s==loc_v.to_s.sub('e','').gsub(/\A0+/,"") #trim leading zeroes #this is a string in scentific notation, or a numerical string with a leading zero #GDocs handles this poorly, need to rewrite write_dst cells by hand with a leading apostrophe for text sheet[row_i+1,col_i+1] = %{'#{loc_v}} re_col_vs << {'row_i'=>row_i+1,'col_i'=>col_i+1,'col_v'=>%{'#{loc_v}}} elsif loc_v.class==Float or loc_v.class==Fixnum if (loc_v - rem_v.to_f).abs>0.0001 "row #{row_i.to_s} col #{col_i.to_s}: Local=>#{loc_v.to_s} , Remote=>#{rem_v.to_s}".oputs errcnt+=1 end elsif rem_v.class==Float or rem_v.class==Fixnum if (rem_v - loc_v.to_f).abs>0.0001 "row #{row_i.to_s} col #{col_i.to_s}: Local=>#{loc_v.to_s} , Remote=>#{rem_v.to_s}".oputs errcnt+=1 end elsif loc_v.to_s.is_time? rem_time = begin Time.parse(rem_v.to_s) rescue nil end if rem_time.nil? || ((loc_v - rem_time).abs>1) "row #{row_i.to_s} col #{col_i.to_s}: Local=>#{loc_v} , Remote=>#{rem_v}".oputs errcnt+=1 end else #"loc_v=>#{loc_v.to_s},rem_v=>#{rem_v.to_s}".oputs begin if loc_v.force_encoding("UTF-8") != rem_v.force_encoding("UTF-8") #make sure it's not an ecoding issue "row #{row_i.to_s} col #{col_i.to_s}: Local=>#{loc_v} , Remote=>#{rem_v}".oputs errcnt+=1 end rescue => exc "#{exc.to_s}".oputs "row #{row_i.to_s} col #{col_i.to_s}: Local=>#{loc_v} , Remote=>#{rem_v}".oputs errcnt+=1 end end end end end end if errcnt==0 if re_col_vs.length>0 sheet.save "rewrote:#{re_col_vs.to_s}".oputs else true end else sheet.save "#{errcnt} errors found in checksum".oputs end end
delete_sheet1()
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 26 def delete_sheet1 sheet = self #delete sheet1 sheet1 = sheet.spreadsheet.worksheet_by_title("Sheet1") || sheet.spreadsheet.worksheet_by_title("Sheet 1") if sheet1 sheet1.delete return true end end
merge(merge_sheet,user_name,crop)
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 55 def merge(merge_sheet,user_name,crop) #write the top left of sheet #with the contents of merge_sheet sheet = self sheet.reload entry = sheet.spreadsheet.acl_entry("#{user_name}@#{Mobilize::Gdrive.domain}") unless entry and ['writer','owner'].include?(entry.role) raise "User #{user_name} is not allowed to write to #{sheet.spreadsheet.title}" end merge_sheet.reload curr_rows = sheet.num_rows curr_cols = sheet.num_cols merge_rows = merge_sheet.num_rows merge_cols = merge_sheet.num_cols raise "zero sized merge sheet" if merge_rows == 0 or merge_cols == 0 #make sure sheet is at least as big as necessary #or as small as necessary if crop is specified if merge_rows > curr_rows or (merge_rows < curr_rows and crop==true) sheet.max_rows = merge_rows sheet.save end if merge_cols > curr_cols or (merge_cols < curr_cols and crop==true) sheet.max_cols = merge_cols sheet.save end batch_start = 0 batch_length = 80 merge_sheet.rows.each_with_index do |row,row_i| row.each_with_index do |val,col_i| sheet[row_i+1,col_i+1] = val end if row_i > batch_start + batch_length sheet.save batch_start += (batch_length+1) end end sheet.save end
read(user)
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 96 def read(user) sheet = self entry = sheet.spreadsheet.acl_entry("#{user}@#{Mobilize::Gdrive.domain}") if entry and ['reader','writer','owner'].include?(entry.role) sheet.to_tsv else raise "User #{user} is not allowed to read #{sheet.spreadsheet.title}" end end
to_tsv(gsub_line_breaks=" ")
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 3 def to_tsv(gsub_line_breaks=" ") sheet = self rows = sheet.rows header = rows.first return nil unless header and header.first.to_s.length>0 #look for blank cols to indicate end of row col_last_i = (header.index("") || header.length)-1 #ignore user-entered line breaks for purposes of tsv reads out_tsv = rows.map do |r| row = r[0..col_last_i].join("\t") row.gsub!(/[\n\r]/,gsub_line_breaks) row = row + "\n" row end.join + "\n" out_tsv.tsv_convert_dates(Mobilize::Gsheet.config['sheet_date_format'], Mobilize::Gsheet.config['read_date_format']) end
write(tsv,user,crop=true)
click to toggle source
# File lib/mobilize-base/extensions/google_drive/worksheet.rb, line 106 def write(tsv,user,crop=true) sheet = self entry = sheet.spreadsheet.acl_entry("#{user}@#{Mobilize::Gdrive.domain}") unless entry and ['writer','owner'].include?(entry.role) raise "User #{user} is not allowed to write to #{sheet.spreadsheet.title}" end tsvrows = tsv.split("\n") #no rows, no write return true if tsvrows.length==0 headers = tsvrows.first.split("\t") batch_start = 0 batch_length = 80 rows_written = 0 curr_rows = sheet.num_rows curr_cols = sheet.num_cols #make sure sheet is at least as big as necessary #or small as necessary if crop if tsvrows.length > curr_rows or (tsvrows.length < curr_rows and crop==true) sheet.max_rows = tsvrows.length sheet.save end if headers.length > curr_cols or (tsvrows.length < curr_rows and crop==true) sheet.max_cols = headers.length sheet.save end #write to sheet in batches of batch_length while batch_start < tsvrows.length batch_end = batch_start + batch_length tsvrows[batch_start..batch_end].each_with_index do |row,row_i| rowcols = row.split("\t") rowcols.each_with_index do |col_v,col_i| sheet[row_i + batch_start + 1, col_i + 1]= %{#{col_v}} end end sheet.save batch_start += (batch_length + 1) rows_written += batch_length if batch_start>tsvrows.length + 1 break end end true end