Extract A Cell from Google Sheet or a CSV

[pranto1902]

Firefox 49.0.2
iMacros VERSION BUILD=8970419
Windows 10 64 Bit

Hi there,
I know It's kinda bad question. But I really need this.I tried harder to create the code using iMacros 12 but I failed.
I tried to search forum but can't find a exact solution.

Let me show you What I need.

Here is a example of google sheet. --> https://docs.google.com/spreadsheets/d/ ... =drive_web

I wanna extract a cell value.

Or,

I got a csv file like this google sheet example.
Same question there.

How Do I Extract a specific cell from a csv or from google shit??!!

I'm new to this stuff.

Thanks

[chivracq]

Code: Select all

Firefox 49.0.2
iMacros VERSION BUILD=8970419
Windows 10 64 Bit

Hi there,
I know It's kinda bad question. But I really need this.I tried harder to create the code using iMacros 12 but I failed.
I tried to search forum but can't find a exact solution.

Let me show you What I need.

Here is a example of google sheet. --> https://docs.google.com/spreadsheets/d/ ... =drive_web

I wanna extract a cell value.

Or,

I got a csv file like this google sheet example.
Same question there.

How Do I Extract a specific cell from a csv or from google shit??!!

I'm new to this stuff.

Thanks

Hum..., nice to see that you "finally" mention your FCI, which you never did in your previous Thread and where you never followed up...!
You are "a bit lucky" I find your Qt about 'GoogleSheets' a bit Interesting, I normally never (try to) help again if you don't answer my Qt's in some previous Thread(s) and of course don't follow up... Would be nice btw if you could still follow up on that old Thread of yours and finish it "a bit correctly" and "useful" for the Forum with a Solution...

>>>

But OK, I did some "quick and dirty" Testing with your GoogleSheet (oh btw...!, I'm not sure Google will be very "happy" you call it "Google shit", ah-ah...!! ), the 'TAG' Mode strangely enough doesn't really record anything "useful" for iMacros to select any Cell one by one... It would still be possible to use the 'TAG' Mode at the 'TABLE' or at some High Level 'DIV' Level, but that would be pretty cumbersome to further isolate the Data Cell by Cell...

But using the 'EVENT' Mode seems to work pretty correctly, by first selecting (with the Mouse) the first Column or even the whole Table (=> Cell 'A1' gets the Focus... which it actually already has when you load the Page for the first time, but I guess you might later want to switch between 2 Tabs in your Browser between the GoogleSheet and the Page/Site where you'll be running your Macro, and that 'A1' Cell might not always have the Focus anymore), and then using the 'Down'/'Up'/'Right'/'Left' Keyboard Arrows to navigate in the Table to the specific Cell that you want to select..., and a 'Ctrl^c' (= OS Clipboard Copy) will copy its Content to your OS Clipboard, directly from that Cell or from the Formula Bar (where the 'TAG' Mode (+ 'EXTRACT=TXT') can also be used if you prefer):

Code: Select all

VERSION BUILD=8820413 RECORDER=FX
TAB T=1
URL GOTO=https://docs.google.com/spreadsheets/d/1t6yelDzGwpX1O99gAXWWR21SM-VgwB-7TickMyZShj8/edit#gid=0
EVENT TYPE=CLICK SELECTOR="#\\30 -fixed>DIV:nth-of-type(1)" BUTTON=0
'EVENT TYPE=CLICK SELECTOR="#t-formula-bar-input>DIV" BUTTON=0
EVENTS TYPE=KEYPRESS SELECTOR="#t-formula-bar-input>DIV" KEYS="[40,40,38]"

'EVENT TYPE=KEYPRESS SELECTOR="#t-formula-bar-input>DIV" CHAR="c" MODIFIERS="ctrl"
EVENT TYPE=KEYPRESS SELECTOR=* CHAR="c" MODIFIERS="ctrl"
PROMPT _{{!CLIPBOARD}}_

=> The 'PROMPT' will display your "somthing2@gmail.com" 2nd Cell.

(Tested on iMacros for FF v8.8.2, Pale Moon v26.3.3 (=FF47), Win10_x64.)

Your GoogleSheet is in 'ReadOnly' Mode for me, the Selectors might be a bit different for you if the Sheet is in 'Edit' Mode for you, but the Principle will be the same...

You might want to give a try to iMacros for CR, I never really tried myself (as I don't like CR and iMacros for CR), but I think that's the "only" Case where iMacros for CR might work better than iMacros for FF, as GoogleDocs "integrate" better with CR than with FF, and I think on CR you can more easily use the 'TAG' Mode to select Cells one by one (and to extract or input them).
(But if using iMacros for CR, you need to write your whole Script using only the 'TAG' Mode, even if the 'EVENT' Mode is also supported on CR (since v8.4.4), the 'MODIFIERS' and 'KEY' Parameters needed for the 'Ctrl^c' and for the Keyboard Arrows are not supported in iMacros for CR..., and if you want to use a '.CSV', you'll need the 'PE' Version ('Personal Edition') with v10.0.2 for CR which is not Free, for FIO/File Access...)

>>>

And for your Qt about a '.CSV', I'll refer you to the Wiki where all you want is extensively documented (=> all '!DATASOURCE'_xxx Commands etc...), + many Examples on the Forum if you search a mini-bit, and you even have some 'Loop-2CSV-xxx.iim' Demo-Macro explaining how to use a '.CSV' as DataSource...

>>>

OK, "good luck", and I hope you'll follow up "a bit better" on this current Thread than on your previous one (that I would still ask you to finish a bit correctly, even after 1 year, ah-ah...!), and that you'll share your Final Script... There are already a few other Threads on the Forum related to GoogleDocs/GoogleSheets, but I don't think any User(s) ever shared their Final Script/Solution, so that would still be useful for the Forum...

[chivracq]

For my own "Record"/Info...: "Useless" User a bit, never follows up on their Threads, 2nd time I tried and 2nd time no Follow-up...
Oh well, fair enough, ah-ah...!
(Maybe one day, that Thread might still be useful for some other User(s)... )

[jaivee]

VERSION BUILD=1005 RECORDER=CR
Chromebook, German
Chrome version 76.0.3809.102
iMacros= for Chrome, free Edtition

Thank you very much for the insight. I have tried to follow up the above, but it seems Google might have changed the functionality so the EVENT does not seem to work anymore (or I am doing something wrong)

The reason why I am building this script / procedure is Vimeo.com
At Vimeo.com, once you do not further pay them an annual fee, they do delete nearly ALL your videos.
So far so understandable, even if it is not publicly displayed in the offer process and when I was uploading >3000 private videos over the last couple of years.
On request they send you a CSV file with links to download your 3000 videos, but this is only valid for 24h and you have to click every single link, unless you upgrade to PRO which is more money. Hence....pretty much a ripp off

So here comes iMacros into play. Since I have a Chromebook I dont have the file access extension (yet, hopefully soon) I would like to reach out to Google Spreadsheets to go through the links and then with ONDOWNLOAD do the trick

Google Spreadsheet does offer an XPATH with a URL

https://docs.google.com/spreadsheets/d/ ... 0&range=A1

but I was not able to integrate it properly into iMacros.

Do you maybe have and idea how to adress the cell? Google blocked the inspect element function.

for the Vimeo procedure I thought of something like that

Code: Select all

' Step 1 adressing the right cell in Google Spreadsheets. A1 at first, then A{{LOOP}} in a later version, once it works
TAB T=1
URL GOTO=https://docs.google.com/spreadsheets/d/1t6yelDzGwpX1O99gAXWWR21SM-VgwB-7TickMyZShj8/edit#gid=0
TAG XPATH=

' Google is offering range=A1 as the xpath but it remains unclear how to translate that into an iMacro accessible link
 
EXTRACT=TXT
PROMPT {{EXTRACT}}

' then the downloading process - by the nature of the link the download should happen automatically 
https://player.vimeo.com/play/1033101[...]&loc=external&context=.&download=1

SET !TIMEOUT_PAGE 300
ONDOWNLOAD FOLDER=* FILE=* WAIT=YES
URL GOTO= {{EXTRACT}}
 WAIT SECONDS=1

thank you very much. your input is highly appreciated

[chivracq]

Code: Select all

VERSION BUILD=1005 RECORDER=CR
Chromebook, German
Chrome version 76.0.3809.102
iMacros= for Chrome, free Edtition

Thank you very much for the insight. I have tried to follow up the above, but it seems Google might have changed the functionality so the EVENT does not seem to work anymore (or I am doing something wrong)

The reason why I am building this script / procedure is Vimeo.com
At Vimeo.com, once you do not further pay them an annual fee, they do delete nearly ALL your videos.
So far so understandable, even if it is not publicly displayed in the offer process and when I was uploading >3000 private videos over the last couple of years.
On request they send you a CSV file with links to download your 3000 videos, but this is only valid for 24h and you have to click every single link, unless you upgrade to PRO which is more money. Hence....pretty much a ripp off

So here comes iMacros into play. Since I have a Chromebook I dont have the file access extension (yet, hopefully soon) I would like to reach out to Google Spreadsheets to go through the links and then with ONDOWNLOAD do the trick

Google Spreadsheet does offer an XPATH with a URL

https://docs.google.com/spreadsheets/d/ ... 0&range=A1

but I was not able to integrate it properly into iMacros.

Do you maybe have and idea how to adress the cell? Google blocked the inspect element function.

for the Vimeo procedure I thought of something like that

Code: Select all

' Step 1 adressing the right cell in Google Spreadsheets. A1 at first, then A{{LOOP}} in a later version, once it works
TAB T=1
URL GOTO=https://docs.google.com/spreadsheets/d/1t6yelDzGwpX1O99gAXWWR21SM-VgwB-7TickMyZShj8/edit#gid=0
TAG XPATH=

' Google is offering range=A1 as the xpath but it remains unclear how to translate that into an iMacro accessible link
 
EXTRACT=TXT
PROMPT {{EXTRACT}}

' then the downloading process - by the nature of the link the download should happen automatically 
https://player.vimeo.com/play/1033101[...]&loc=external&context=.&download=1

SET !TIMEOUT_PAGE 300
ONDOWNLOAD FOLDER=* FILE=* WAIT=YES
URL GOTO= {{EXTRACT}}
 WAIT SECONDS=1

thank you very much. your input is highly appreciated

Oh...!, I first thought you were the "useless" original @OP from this Thread who never followed up, ah-ah...!
Alright, so my "(Maybe one day, that Thread might still be useful for some other User(s)... )" is apparently coming a bit true, good-good...
But you say it doesn't "really" work anymore..., hum, well, I'm not "too" surprised, the Interface keeps changing "constantly"...

But-but-but..., hum..., I would have a much easier Approach in your Case, I would think..., ah-ah...!

So, if I understood correctly, you've received a "real" '.CSV' File from 'Vimeo', that you managed to open from your ChromeBook or some Desktop/Laptop, and then using some Text Editor like 'Notepad' or some Spreadsheet Software like 'Excel'/'OpenOffice'/'LibreOffice', and you copied the whole Content of that '.CSV' to some New GoogleSheet.
Yeah, OK, but here comes my "better" Approach, ah-ah...!

If you can go back to opening the '.CSV' in 'Excel' or similar App ('OO' or 'LO'), but not 'Notepad'...
Shift all existing Content to the Right by inserting a first Col on the complete Left of your Data Sheet.
You give a "Vid_1" Name to the 1st Cell corresponding to your 1st Video Link.
Click on the Bottom Right Corner of that Cell (you might need to press 'Ctrl' at the same time...) and drag it down until Row 3000, and you should get [Vid_1,Vid_2,...,Vid_3000] in that 1st Col.
(Really use "Vid_1"/"Vid_2" etc and not "Vid_0001"/"Vid_0002" etc...)

Give those 2 Cols a different Colour...!! (Probably not really needed in your Case, but it's a Trick that can be useful... )

And now the "big Trick"...!: Save your Sheet as 'HTML'...! (and not as '.CSV' or any other Format the Software will want you to use...)
=> Now you have your complete original '.CSV'/DataSource converted to a (Local) '.HTML' File that you can simply open in your Browser in 'TAB_1', from which you can extract one by one all 3000 Video Links to then download them all from 'TAB_2', and maybe a 2nd Tab is not even needed if you use 'SAVETARGETAS' as I guess all Links will be automatically made as Hyperlinks by CR...

But your Script should look like stg like that...:

Code: Select all

TAB T=1
SET !LOOP 1
TAG POS={{!LOOP}} TYPE=TD ATTR=TXT:Vid_{{!LOOP}}
TAG POS=R1 TYPE=A ATTR=TXT:* CONTENT=EVENT:SAVETARGETAS
WAIT SECONDS=10

Not tested of course...

Using the 'Free' Version of v10.0.5 for CR, you cannot use the 'ONDOWNLOAD' Command to control the Name + Location where you want to download all the Videos..., and Looping is limited to 100, but once you've done the 1st Batch of Vids [1-100], you modify the "SET !LOOP 1" into "SET !LOOP 101", hum-hum-hum [...], I'm not sure where the Check on the Loop=Max 100 is for the 'Free' Limitation, then OK, this one should work, and you each time loop it 100 times from 1 to 100, and you only modify the "SET !VAR1 0" to "100" => "200" etc...:

Code: Select all

TAB T=1
SET !VAR1 0
ADD !VAR1 {{!LOOP}}
TAG POS={{!VAR1}} TYPE=TD ATTR=TXT:Vid_{{!VAR1}}
TAG POS=R1 TYPE=A ATTR=TXT:* CONTENT=EVENT:SAVETARGETAS
WAIT SECONDS=10

[jaivee]

Hi - thank you very much for your swift reply and great ideas!!

It took me a bit to play around and to figure out, basically also cos the links Vimeo provides are always valid only 24hrs.
Great idea with the *.html file

With XPATH I was able to adress the link directly

Code: Select all

SET !EXTRACT_TEST_POPUP NO
SET !ERRORIGNORE YES

TAB T=1
SET !VAR1 0
ADD !VAR1 {{!LOOP}}
TAG XPATH="/html/body/div/table/tbody/tr[{{!VAR1}}]/td/div/a" EXTRACT=TXT
TAB OPEN 
TAB T=2 
URL GOTO={{!EXTRACT}}

' In general when the TAB is closed, the download is aborted so we set the timeout and the wait quite long 
SET !TIMEOUT_DOWNLOAD 500
WAIT SECONDS=60

TAB CLOSE
SET !EXTRACT NULL

The code works very well.
Nevertheless, I reckon that the Chromebook Chrome (free) version without ONDOWNLOAD does not provide a practical solution.
It seems that - since you don't have any success control whatsoever over the download - too many processes are started at the same time and - even if the TIMEOUT_DOWNLOAD is very long - some downloads return errors. I assume this is also due to the fact that the files are quite big.

Thanks again for your input. Very helpful.
I will try to find a Win 10 computer now which provides

Code: Select all

ONDOWNLOAD FOLDER=* FILE=* WAIT=YES

and will run the code there