Skip to content
This repository was archived by the owner on Dec 29, 2022. It is now read-only.

Commit af46943

Browse files
qpythonnqpythonn
committed
first commit of a script that loop over a text file to update a google spread sheet
1 parent 1df4e1e commit af46943

1 file changed

Lines changed: 206 additions & 0 deletions

File tree

‎samples/spreadsheets/spreadsheet_UpdateCells.py‎

Lines changed: 206 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,206 @@
1+
#!/usr/bin/python
2+
3+
# This script is editing an already existing google spreadsheet.
4+
# It loops over a file and parses each line to get a dataset and its corresponding number of events
5+
# At each use you will be prompt the select the google spread sheet you wouldlike to edit and the sheet number of it.
6+
7+
8+
9+
try:
10+
from xml.etree import ElementTree
11+
except ImportError:
12+
from elementtree import ElementTree
13+
import gdata.spreadsheet.service
14+
import gdata.service
15+
import atom.service
16+
import gdata.spreadsheet
17+
import time
18+
import atom
19+
import getopt
20+
import sys
21+
import string
22+
23+
24+
class SimpleCRUD:
25+
26+
def __init__(self):
27+
self.gd_client = gdata.spreadsheet.service.SpreadsheetsService()
28+
self.gd_client.email = "qpython@vub.ac.be"
29+
self.gd_client.password = "Testfaco"
30+
self.gd_client.source = 'Spreadsheets GData Sample'
31+
self.gd_client.ProgrammaticLogin()
32+
self.curr_key = ''
33+
self.curr_wksht_id = ''
34+
self.list_feed = None
35+
36+
def _PromptForSpreadsheet(self):
37+
# Get the list of spreadsheets
38+
feed = self.gd_client.GetSpreadsheetsFeed()
39+
self._PrintFeed(feed)
40+
print "among the list above, choose the desired document you want to work with by choosing the corresponding number. Be very carefull as the file will be overwritten by the upcoming changes!!!"
41+
# input = raw_input('\nSelection: ')
42+
input = raw_input('\nSelection: ')
43+
# input = str(0) # hardcoded
44+
id_parts = feed.entry[string.atoi(input)].id.text.split('/')
45+
self.curr_key = id_parts[len(id_parts) - 1]
46+
47+
def _PromptForWorksheet(self):
48+
# Get the list of worksheets
49+
feed = self.gd_client.GetWorksheetsFeed(self.curr_key)
50+
"among the list above, choose the desired sheet you want to work with by choosing the corresponding number. Be very carefull as the file will be overwritten by the upcoming changes!!!"
51+
self._PrintFeed(feed)
52+
input = raw_input('\nSelection: ')
53+
# input = str(0) # hardcoded
54+
id_parts = feed.entry[string.atoi(input)].id.text.split('/')
55+
self.curr_wksht_id = id_parts[len(id_parts) - 1]
56+
57+
# for it in range (0,10)
58+
def _PromptForCellsAction(self, row, col, input_value):
59+
self._CellsUpdateAction(row, col, input_value)
60+
61+
def _PromptForListAction(self):
62+
print ('dump\n'
63+
'insert {row_data} (example: insert label=content)\n'
64+
'update {row_index} {row_data}\n'
65+
'delete {row_index}\n'
66+
'Note: No uppercase letters in column names!\n'
67+
'\n')
68+
input = raw_input('Command: ')
69+
command = input.split(' ' , 1)
70+
if command[0] == 'dump':
71+
self._ListGetAction()
72+
elif command[0] == 'insert':
73+
self._ListInsertAction(command[1])
74+
elif command[0] == 'update':
75+
parsed = command[1].split(' ', 1)
76+
self._ListUpdateAction(parsed[0], parsed[1])
77+
elif command[0] == 'delete':
78+
self._ListDeleteAction(command[1])
79+
else:
80+
self._InvalidCommandError(input)
81+
82+
def _CellsGetAction(self):
83+
# Get the feed of cells
84+
feed = self.gd_client.GetCellsFeed(self.curr_key, self.curr_wksht_id)
85+
self._PrintFeed(feed)
86+
87+
def _CellsUpdateAction(self, row, col, inputValue):
88+
entry = self.gd_client.UpdateCell(row=row, col=col, inputValue=inputValue,
89+
key=self.curr_key, wksht_id=self.curr_wksht_id)
90+
if isinstance(entry, gdata.spreadsheet.SpreadsheetsCell):
91+
print 'Updated!'
92+
93+
def _ListGetAction(self):
94+
# Get the list feed
95+
self.list_feed = self.gd_client.GetListFeed(self.curr_key, self.curr_wksht_id)
96+
self._PrintFeed(self.list_feed)
97+
98+
def _ListInsertAction(self, row_data):
99+
entry = self.gd_client.InsertRow(self._StringToDictionary(row_data),
100+
self.curr_key, self.curr_wksht_id)
101+
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
102+
print 'Inserted!'
103+
104+
def _ListUpdateAction(self, index, row_data):
105+
self.list_feed = self.gd_client.GetListFeed(self.curr_key, self.curr_wksht_id)
106+
entry = self.gd_client.UpdateRow(
107+
self.list_feed.entry[string.atoi(index)],
108+
self._StringToDictionary(row_data))
109+
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
110+
print 'Updated!'
111+
112+
def _ListDeleteAction(self, index):
113+
self.list_feed = self.gd_client.GetListFeed(self.curr_key, self.curr_wksht_id)
114+
self.gd_client.DeleteRow(self.list_feed.entry[string.atoi(index)])
115+
print 'Deleted!'
116+
117+
def _StringToDictionary(self, row_data):
118+
dict = {}
119+
for param in row_data.split():
120+
temp = param.split('=')
121+
dict[temp[0]] = temp[1]
122+
return dict
123+
124+
def _PrintFeed(self, feed):
125+
for i, entry in enumerate(feed.entry):
126+
if isinstance(feed, gdata.spreadsheet.SpreadsheetsCellsFeed):
127+
print '%s %s\n' % (entry.title.text, entry.content.text)
128+
elif isinstance(feed, gdata.spreadsheet.SpreadsheetsListFeed):
129+
print '%s %s %s' % (i, entry.title.text, entry.content.text)
130+
# Print this row's value for each column (the custom dictionary is
131+
# built using the gsx: elements in the entry.)
132+
print 'Contents:'
133+
for key in entry.custom:
134+
print ' %s: %s' % (key, entry.custom[key].text)
135+
print '\n',
136+
else:
137+
print '%s %s\n' % (i, entry.title.text)
138+
139+
def _InvalidCommandError(self, input):
140+
print 'Invalid input: %s\n' % (input)
141+
142+
def Run(self):
143+
f = open('output.txt', 'r')
144+
self._PromptForSpreadsheet()
145+
self._PromptForWorksheet()
146+
#input = raw_input('cells or list? ')
147+
print "replace cell by cell for now on"
148+
input='cells'
149+
if input == 'cells':
150+
151+
# getting time info at the
152+
start_date= time.strftime('%d/%m/%Y')
153+
start_time= time.strftime('%H:%M:%S')
154+
155+
# erase previous date information
156+
self._PromptForCellsAction("1","1","This document is BEING edited by a script right now! Please do not modify it!")
157+
self._PromptForCellsAction("2","1","")
158+
159+
# edit the "title" of the column
160+
self._PromptForCellsAction("4","2", "dataset")
161+
self._PromptForCellsAction("4","11", "nEvents")
162+
163+
# loop over the list of dataset
164+
iterator=1
165+
for line in f:
166+
# do the parsing
167+
p1=line.split("dataset ",1)
168+
p2=p1[1].split(" = ",1)
169+
p3=p2[0].split("/", 3)
170+
dataset=p3[1]+"/"+p3[2]
171+
nEvents=p2[1]
172+
173+
# edit the dataset name column
174+
self._PromptForCellsAction(str(iterator+4),"2", dataset)
175+
# edit the nEvents column
176+
self._PromptForCellsAction(str(iterator+4),"11", nEvents)
177+
if (iterator % 5 == 0):
178+
self._PromptForCellsAction("2","1","last change on the "+time.strftime('%d/%m/%Y')+" at "+time.strftime('%H:%M:%S'))
179+
iterator=iterator+1
180+
# end of loop
181+
182+
183+
# get time information
184+
end_date= time.strftime('%d/%m/%Y')
185+
end_time= time.strftime('%H:%M:%S')
186+
187+
# print date and time information on the first cell of the document
188+
self._PromptForCellsAction("1","1","This document has been edited by a script on the "+start_date+" at "+start_time)
189+
self._PromptForCellsAction("2","1","The last change has been done on the "+end_date+" at "+end_time)
190+
191+
elif input == 'list':
192+
while True:
193+
self._PromptForListAction()
194+
195+
196+
def main():
197+
# parse command line options
198+
key = ''
199+
# Process options
200+
201+
sample = SimpleCRUD()
202+
sample.Run()
203+
204+
205+
if __name__ == '__main__':
206+
main()

0 commit comments

Comments
 (0)