My deepest appreciation to all your efforts. You are a life saver. Can you please add a series about google classroom since most schools are going towards online learning. Specifically about getting students data from google classrooms
Excellent video, as always. I was wondering have you ever made a way to port Google Doc data (maybe a paragraph or an entire page) into google sheets? I've only seen the other way (Google Sheets - > Docs).
Hi, thanks a lot for series of tutorials, it's helping, but i have a question for your tutorial, i'm getting stuck to getting file['id'], idk why every time i ran the code, print['id'] is didn't same with the actual id if i open the sheets. can you help me to debugging on this?
Thank you for the video it helped a lot just out of curiosity can we have that script on auto run after every 15 mins so that if new data is added that will get updated in our master file...?????
Hi, How can we print Combined Sheet names in A1:A and sheet names in B1:B ? When so many Worksheets and sheets are combined, we cannot understand where the data is coming from. I will be glad if you help. Thank you for your help.
Hello, I asked this question under your other video. Will you help or do you just read and pass the comments? I enjoy watching your videos and I look forward to your new videos. I hope you can help me with this. (I have 450 workbooks. I want to combine them and use them as database, but I need to print the workbook name in an empty column in the merged workbook. Otherwise, merging the workbooks does not make any sense. I hope you will write an answer that will solve my comment.)
I'm trying to generate report from my Google Sheet. So far, I prepare a template and publish the report as an HTML page (like your webapp tutorial). Its working perfectly. Now I want to save this published page as a PDF in my Google Drive or Local Hard-disk. What is the best method for this? Can you suggest me any possible way?
Hi! Thank you for this tutorial. Its been extremely helpful. However, I have single sheets in multiple folders that I need to merge. Can you guide me on how I can do this. I am getting stuck after I manage to find all the folder ids. I dont know how to search in each folder id for the right file. Any guidance would be really appreciated! TIA
Great, I've been using your method for a while, it's really helpful. However, in recent times, when I change data of old sheets and update the data in the new sheet, the result is that the old data is still saved to the master sheet even though I check that it has changed. . Let me ask the cause and how to fix it.
Hello. Thank you for this wonderful video ! It really tackle what I need. Is there a way to execute the script from google sheet when it opens or create a button to update on demand ? Thank you for your feedback.
Is there any way to use columns as just columns instead of rows? Columns=rows is not what I want. I want to import the Spreadsheet in it's normal format. Thank you.
Hello. Does it work with Google Workspace ? I have did this and I have no problem using it to compile files from my Drive, however as soon as I want to do it with Drives that are shared, it doesn't work anymore. Is there a way to solve this problem ?
hi nice video, i have a question!! when i run the 3rd part of your code googlecolab says: AttributeError Traceback (most recent call last) in () ----> 1 worksheet = gc.open_by_key('1doiAYEqtOqYHaKM4HAmU-mHC_VgU28-q').sheetl 2 rows = worksheet.get_all_values() 3 df = pd.DataFrame.from_records(rows[1:],columns=rows[0]) AttributeError: 'Spreadsheet' object has no attribute 'sheetl' i dont know what to do to make ir right thanks
I received this error message:" list indices must be integers or slices, not str" for this line "sh = gc.open_by_key(file['id']).worksheet("Dashboard") and don't know how to fix it. Can anyone help, please?
My full script is: listed = drive.ListFile({'q': "'1QFPp0SbcWPdL01aTIcSfIpdCxfO8Nt4g' in parents "}) for file in listed: sh = gc.open_by_key(file['id']).worksheet("Dashboard") rows = sh.get_all_values() df= pd.DataFrame.from_records(rows[1:],columns=rows[0]) listofFrames.append(df)
Sorry newbi here, please can you teach how to make google collab keep running in the back.end so we don't need to keep hit 'RUN' while we editing the sheet everyday?
Google Colab is a temporary environment, you can't keep running it in the backend. You can setup Python on your own computer and keep it running if you want or you can rent a server from Google or other provider, install python on it and keep it running 24/7.
Hi, Thanks a lot for series of tutorials, it is helping many of the professionals. I have one concern between excel and spreadsheet. 1.is there any possibility to automatically import the live data Microsoft excel to spreadsheet using script?? 2. is there any possibility to pull the data from Google spreadsheet to excel using power query. I believe this is most awaited option for the many of the people to work with the data seemlessly. Thank you as always
Would you be willing to help me with something similar? If not, do you have someone you can recommend? I have a project that I've been using Combine Sheets from Ablebits with and it continues to fail. I spend too much time fixing the breaks than what's it's worth. This may be more beneficial.
Thanks you so much for this. I have a question - Is there any advantage of doing this in Google Colab over GAS? Just want to know the different of how these 2 tools can achieve. Thanks!
Sometimes. With Google Colab you can run tasks over multiple threads when they get slow. You won't be able to do that with GAS, at least for the time being.
Hi, very intersting as usual... I have a question: For my school, I created from google sheets 1000 Gdocs files (autocrat). How can I share them with teachers from google sheets? Is there a automation that allows to easily manage the sharing of a document to some teachers and not to others? Thanks for your answer...
Excellent videos as always but as a Javascript/Python expert what environment do you recommend ? Although I'm a Python lover I see too way complicated and elaborated ? all the initial setup required in Google Colab.
Hi, is it possible to have a Loop that point to Sheet 2 I tried to modify the code to worksheet = gc.open_by_key(file['id']).sheet2 but it just doesn't works, anybody can help? The second worksheet name is "WP Reconciliation"
@@ExcelGoogleSheets What if you don't know the name of the worksheet and its different each time can you do something like worksheet = gc.open_by_key(file['id']).worksheet(0) ?
Hello, can you help me with this problem? I have a dataset that one column has tags, like: A B,C D,A C,D,B D How can I filter this like a list? A B C D Thanks in advance!
@@ExcelGoogleSheets docs.google.com/spreadsheets/d/1HguiNtj68dMHUEyf9DOvagybBnM5BqMVGiNauLv2AoM/edit?usp=sharing I created this Sheet to explain it better.
wsResult.update("A1",combinedData,value_input_option="USER_ENTERED") AttributeError: 'Worksheet' object has no attribute 'update' Not working the update attribute, why?
![NLE Choppa - Or What ft. 41 (Orchestra Mix) [Official Music Video]](http://i.ytimg.com/vi/biOFASEgSOM/mqdefault.jpg)
My deepest appreciation to all your efforts. You are a life saver. Can you please add a series about google classroom since most schools are going towards online learning. Specifically about getting students data from google classrooms
Perfect! First thump up then watch the video.
Please keep doing these Colab videos, you've already taught us apps script
Hey, this is exactly what I was looking for. Thank you so much!
Excellent video, as always. I was wondering have you ever made a way to port Google Doc data (maybe a paragraph or an entire page) into google sheets? I've only seen the other way (Google Sheets - > Docs).
awesome！！！thank you very much
Hi, thanks a lot for series of tutorials, it's helping, but i have a question for your tutorial, i'm getting stuck to getting file['id'], idk why every time i ran the code, print['id'] is didn't same with the actual id if i open the sheets. can you help me to debugging on this?
Thank you for the video it helped a lot just out of curiosity can we have that script on auto run after every 15 mins so that if new data is added that will get updated in our master file...?????
Hi,
How can we print Combined Sheet names in A1:A and sheet names in B1:B ?
When so many Worksheets and sheets are combined, we cannot understand where the data is coming from. I will be glad if you help. Thank you for your help.
Hello,
I asked this question under your other video. Will you help or do you just read and pass the comments?
I enjoy watching your videos and I look forward to your new videos. I hope you can help me with this.
(I have 450 workbooks. I want to combine them and use them as database, but I need to print the workbook name in an empty column in the merged workbook. Otherwise, merging the workbooks does not make any sense. I hope you will write an answer that will solve my comment.)
I'm trying to generate report from my Google Sheet. So far, I prepare a template and publish the report as an HTML page (like your webapp tutorial). Its working perfectly.
Now I want to save this published page as a PDF in my Google Drive or Local Hard-disk. What is the best method for this? Can you suggest me any possible way?
Awesome just wondering if i have multiple files that in different folders, how could i merge that into 1 master sheet?
Thank you for sharing this video!
One question, can we insert one column "source name" that tells name of workbook?
Hi! Thank you for this tutorial. Its been extremely helpful. However, I have single sheets in multiple folders that I need to merge. Can you guide me on how I can do this. I am getting stuck after I manage to find all the folder ids. I dont know how to search in each folder id for the right file. Any guidance would be really appreciated! TIA
Great, I've been using your method for a while, it's really helpful.
However, in recent times, when I change data of old sheets and update the data in the new sheet, the result is that the old data is still saved to the master sheet even though I check that it has changed. .
Let me ask the cause and how to fix it.
Hello. Thank you for this wonderful video ! It really tackle what I need. Is there a way to execute the script from google sheet when it opens or create a button to update on demand ?
Thank you for your feedback.
Is there any way to use columns as just columns instead of rows? Columns=rows is not what I want. I want to import the Spreadsheet in it's normal format. Thank you.
Thank you!
Thank you it helped me a lot but sometimes my data gets an extra space after any word why is that case...?
Hello. Does it work with Google Workspace ? I have did this and I have no problem using it to compile files from my Drive, however as soon as I want to do it with Drives that are shared, it doesn't work anymore. Is there a way to solve this problem ?
hi nice video, i have a question!! when i run the 3rd part of your code googlecolab says:
AttributeError Traceback (most recent call last)
in ()
----> 1 worksheet = gc.open_by_key('1doiAYEqtOqYHaKM4HAmU-mHC_VgU28-q').sheetl
2 rows = worksheet.get_all_values()
3 df = pd.DataFrame.from_records(rows[1:],columns=rows[0])
AttributeError: 'Spreadsheet' object has no attribute 'sheetl'
i dont know what to do to make ir right
thanks
it's sheet1, not sheetl
I received this error message:" list indices must be integers or slices, not str" for this line "sh = gc.open_by_key(file['id']).worksheet("Dashboard") and don't know how to fix it. Can anyone help, please?
My full script is:
listed = drive.ListFile({'q': "'1QFPp0SbcWPdL01aTIcSfIpdCxfO8Nt4g' in parents "})
for file in listed:
sh = gc.open_by_key(file['id']).worksheet("Dashboard")
rows = sh.get_all_values()
df= pd.DataFrame.from_records(rows[1:],columns=rows[0])
listofFrames.append(df)
Sorry newbi here,
please can you teach how to make google collab keep running in the back.end so we don't need to keep hit 'RUN' while we editing the sheet everyday?
Google Colab is a temporary environment, you can't keep running it in the backend. You can setup Python on your own computer and keep it running if you want or you can rent a server from Google or other provider, install python on it and keep it running 24/7.
Hi, Thanks a lot for series of tutorials, it is helping many of the professionals. I have one concern between excel and spreadsheet.
1.is there any possibility to automatically import the live data Microsoft excel to spreadsheet using script??
2. is there any possibility to pull the data from Google spreadsheet to excel using power query.
I believe this is most awaited option for the many of the people to work with the data seemlessly.
Thank you as always
Hi, it's possible to do this in reverse?
From a master sheet create new workbooks based on: sales, brand, month, etc in a new folder?
Yes. Video coming up next week.
Would you be willing to help me with something similar? If not, do you have someone you can recommend? I have a project that I've been using Combine Sheets from Ablebits with and it continues to fail. I spend too much time fixing the breaks than what's it's worth. This may be more beneficial.
Thanks you so much for this. I have a question - Is there any advantage of doing this in Google Colab over GAS?
Just want to know the different of how these 2 tools can achieve. Thanks!
Sometimes. With Google Colab you can run tasks over multiple threads when they get slow. You won't be able to do that with GAS, at least for the time being.
@@ExcelGoogleSheets Thank you!
what to do incase of excel file or csv in Drive folder instead of googlesheet????
Same process.
Hi, I have a similar case. If you've been successful at executing this project, can you please help me with mine?
Hi, very intersting as usual... I have a question: For my school, I created from google sheets 1000 Gdocs files (autocrat). How can I share them with teachers from google sheets? Is there a automation that allows to easily manage the sharing of a document to some teachers and not to others?
Thanks for your answer...
Excellent videos as always but as a Javascript/Python expert what environment do you recommend ? Although I'm a Python lover I see too way complicated and elaborated ? all the initial setup required in Google Colab.
Depends. Python is better for slow running tasks.
its not realtime update at master sheet ?
Hi, is it possible to have a Loop that point to Sheet 2
I tried to modify the code to worksheet = gc.open_by_key(file['id']).sheet2
but it just doesn't works, anybody can help?
The second worksheet name is "WP Reconciliation"
Is it the second sheet or sheet called sheet2
?
@@ExcelGoogleSheets no, the second worksheet name is "WP Reconciliation"?
@@RalphNgOfficial Try gc.open_by_key(file['id']).worksheet("WP Reconciliation")
@@ExcelGoogleSheets What if you don't know the name of the worksheet and its different each time can you do something like worksheet = gc.open_by_key(file['id']).worksheet(0) ?
IndexError: list index out of range - Getting this error when running the loop any suggestions :)
listed = drive.ListFile({'q': "'ddddddddddddddddddddddddddddF' in parents and mimeType = 'application/vnd.google-apps.spreadsheet'"}).GetList()
for file in listed:
worksheet = gc.open_by_key(file['id']).sheet1
rows = worksheet.get_all_values()
df = pd.DataFrame.from_records(rows[1:],columns=rows[0])
listOfFrames.append(df)
Need more context
Hello, can you help me with this problem?
I have a dataset that one column has tags, like:
A
B,C
D,A
C,D,B
D
How can I filter this like a list?
A
B
C
D
Thanks in advance!
What you mean by filter? You need a unique list of tags? How long is the column? How many rows?
@@ExcelGoogleSheets
docs.google.com/spreadsheets/d/1HguiNtj68dMHUEyf9DOvagybBnM5BqMVGiNauLv2AoM/edit?usp=sharing
I created this Sheet to explain it better.
=sort(unique(transpose(split(TEXTJOIN(",",true,A2:A6),","))))
@@ExcelGoogleSheets Thanks a lot!!!!!!
Can anyone please tell me how to link two google notebooks
what is "google notebooks"?
wsResult.update("A1",combinedData,value_input_option="USER_ENTERED")
AttributeError: 'Worksheet' object has no attribute 'update'
Not working the update attribute, why?
run the install for the library to get the new version.