Besides the clarity of your explanations and examples, you show the strategies to figure out new ways to approach a problem. You are not afraid to try a technique that doesn't work at first, allowing us to see how to think through a bad result while seeking a viable solution. Yes, keep stretching the limits for us.
I rarely ever comment on instructional videos. But this is by far the best instructional video I have ever watched. I am familiar with some things, I know things can be done, just knowing which code to use is the hard part to figure it out. You actually described it all, and gave reasons for the code as you went along. I was able to split a 28,000 row tab, into 287 different tabs. The only issue I found was, a Google timeout of 6 minutes per execution of the script. I looked into work arounds to add to your code, but determined it was easier for me to just run this code about 12 different times to complete the split process. Kudos. Thanks.
my wife has now become very skeptical and thinks I'm having an affair because I spend so much time in my room. BUT i can´t get enough. THANK YOU (again!) for these great tutorials AND content.
I tried to get an array of results out of column values like you did in this video, but the way I did it is not as clean and simple as yours. Thanks a lot. I always learn something from your tutorials.
This video was amazing. You are really bringing smart solutions to our problems. I loved this video. Looking for such content & video in future. Waiting for next video. I appericate your work.
Great Lesson! Found out the Hard Way that an Apostrophe is not the same as a Reverse Tick!! The one thing I did see was that Column Width should be Automated!!!
I'm curious, I've tried Googling it, however how would you do this and create a new workbook instead? My use case is very similar, whereby I have three tabs: an FAQ/Introduction tab (no change/duplicate it in each workbook), a source data tab (almost exact challenge listed here), and a transformation tab (duplicate, only going to use importrange to copy down values). Essentially, I need to create 60 different workbooks per "sales rep" versus 60 different sheets within the same workbook. Each workbook would have the same 3 tabs and only the source tab would be filtered down using the methodology outlined here. Edit: As an additional note - my company authorization does not permit me to use Google Collab, so just to preface, I can't use that solution.
Beneficial video learned a lot from this above video. This video is very useful in creating new sheets from the master sheet but I have one question how to recall newly generated sheets in more functions where we can put update data
Thanks this has been so helpful! I have a question: There is a data validation built into the last two columns of my sheets. Example: Choose A-C on dropdown. Is there anyway to keep the data validation from the master sheet and copy it to the rest of the sheets?
Thank you for this well throught out instructional video. You did an amazing job of making this complex process seem approchable and atainable. is there any chance you have the code somewhere that can be copied? It would be very helpful not to have to type it all out from the screen.
Oh ya Sir. I want to show you my new spreadsheet, I made this to distinguish data from two columns : docs.google.com/spreadsheets/d/1XAi9gnVo4ONO-CGJ-RTRdO1ZDi0g48kHVAExfzrn0uE/edit?usp=sharing
hi, i have 600 different value that I want to split into 600 different worksheet, since 1 google workbook maximum capacity is 5M cells, I'm affraid it wont't fit. Is it possible to split it into different workbook? thanks
Thanks so much for the video! Is there are way to delete the rows in the Sales Data master sheet once they are transferred to the new sheet? For example, I am trying to move a particular rows of clients from an "Active Job" sheet to a "Completed Jobs" sheet once once those clients status' in a drop down menu are updated to "Completed"
Nice. If you go back & make changes to the master sales data sheet, will the changes update in the individual sales people sheets or will you need to run the script again?
Awesome video. Instead of multiple worksheet can we do the same thing with multiple workbook. I mean split data from master to new workbook with sales person name if possible. I have to do the same thing but instead of worksheet i want to do it for different workbook.
How would you split a sheet into multiple sheets based on values from TWO columns? For example A:Brand and B:Model, or A:Region and B:Department. Would you split sheets for each A value as demonstrated and then repeat the process using those A sheets to split into AB sheets? A demo video would be very helpful!
Well explained! Question: Can someone tell me how I auto populate new tables based on time? every month a new table, collecting all inputs from the source sheet. I assume i have to replace "const uniqueSalesPeople" section with a filter that looks at a timestamp at the source sheet. How do I formulate this
I ended up combining this with a few other scripts I find, modified, failed, watched this some more and came up with this code (below) Victory! It works and will save me and my team so much time. Thanks again!.
function onOpen(e) { SpreadsheetApp.getUi() .createMenu('Create Sheets') .addItem('By Region', 'ByRegion') .addItem('By District', 'ByDistrict') .addToUi(); } function ByRegion(){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sourceWs = ss.getSheetByName("INDEX"); // 2 is second row 8 is column H where I have region names const Region = sourceWs .getRange (2,8,sourceWs.getLastRow()-1,1) .getValues() .map(ds => ds[0]); const UniqueRegion = [...new Set(Region)]; const currentSheetNames = ss.getSheets().map (s => s.getName()); let ws UniqueRegion.forEach(RegionName => { if(!currentSheetNames.includes(RegionName)){ ws = null; ws = ss.insertSheet (); ws.setName (RegionName); ws.getRange("A2").setFormula(`FILTER(INDEX!A2:H,INDEX!H2:H = "${RegionName}")`); sourceWs.getRange("A1:H1").copyTo(ws.getRange("A1:H1"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false); // Add Borders getRange(row, column, numRows, numColumns) ws.getRange(1,1,ws.getLastRow(),ws.getLastColumn()).setBorder(true, true, true, true, true, true);
//Rezize Columns ws.autoResizeColumns(1, ws.getLastColumn()); //Resize Picture Colums E 5th column and F 6th colum setColumnWidths(startColumn, numColumns, width) change the last number if you want wider ws.setColumnWidths( 5, 2, 30); } //if region name tab does not exist }); // for each loop through the list of regions } // close byRegion function function ByDistrict(){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sourceWs = ss.getSheetByName("INDEX"); // 2 is second row 7 is column G where I have district names const District = sourceWs .getRange (2,7,sourceWs.getLastRow()-1,1) .getValues() .map(ds => ds[0]); const UniqueDistrict = [...new Set(District)]; const currentSheetNames = ss.getSheets().map (s => s.getName()); //ws is the newly created worksheet let ws UniqueDistrict.forEach(DistrictName => { if(!currentSheetNames.includes(DistrictName)){ ws = null; ws = ss.insertSheet (); ws.setName (DistrictName); ws.getRange("A2").setFormula(`FILTER(INDEX!A2:F,INDEX!G2:G = "${DistrictName}")`); sourceWs.getRange("A1:F1").copyTo(ws.getRange("A1:F1"),) // Add Borders getRange(row, column, numRows, numColumns) ws.getRange(1,1,ws.getLastRow(),ws.getLastColumn()).setBorder(true, true, true, true, true, true);
//Rezize Columns ws.autoResizeColumns(1, ws.getLastColumn()); //Resize Picture Colums E 5th column and F 6th colum setColumnWidths(startColumn, numColumns, width) change the last number if you want wider ws.setColumnWidths( 5, 2, 30); // Center Picture Colums E 5th column and F 6th getRange(row, column, numRows, numColumns) ws.getRange(1,5,1,2).setHorizontalAlignment("center"); } //if district name tab does not exist }); // for each loop through the list of districts } // close createSheets function
You can’t update anything under the filter function unless you update it from the original data sheet. I would filter it by date on the original and find it that way.
@@BustaCap i see. I think what i need is distribution of rows to each sales rep from a master file to a separate sheet they can update and see changes in master. Super thanks for this!
LOVE this tutorial! How can I get it to pull from multiple worksheets based on the same value in both? My code is: =filter('K-12 Intake Response Form'!C2:AJ,'K-12 Intake Response Form'!A2:A="HE").............but I want it to also pull from my '2020 Y5s' using the same "HE" column value.
Very great video ! One question : I have tried to update data on a tab but when I do it, all the data from the tab dissapear. Is there a way to update the data on the tabs created that automatically update the master file ? In other words,how to ensure that data update work in both ways ?
Thank you so much! I've already tried it and its working. However, what if I'll insert new name of salesperson on the data? I've tried the existing name in salesperson and data will be automatically copy to its corresponding sheet. However, when a new name is inserted, there will be no sheet to be added corresponding to a new name. What should I do for this situation? Thank you so much for the help!
My first guess would be go back and run the script again, as you did the first time to get it to work. It will skip all the ones that are created, and only create new tabs for the newly added salespeople since the last time you ran the script.
@@ExcelGoogleSheets How do I set a trigger to update the subtabs only at a certain date (every Friday) even when changes are made to the master daily? Thanks!
Every time I run the script to split data into separate sheets I see that the new tab is initially numbered before it takes the text label. Will there be a performance hit the 1000th time I run this and we're on tab 5000? Is there a script to reset the tab numbers count when everyone has closed the sheet at the end of the day?
Thanks for the video! There is a blank value for my "uniqueSalesPeople". How to remove that? Also when I am running the app again the scripts stops first with an error saying the sheet name already exists. If a new salesperson is added I am not able to add the sheets using the script as it stops at the first iteration.
Hello, I have followed your tutorial line by line and my code identical to yours, but it keep giving me exception error on line 22, ws.setName();, saying a worksheet with that name already exists? I know its supposed to loop and not create any worksheet that is already in place and just update the data, but its not doing that and stops at this error. Please help. Thanks for your helpful tutorial btw! ps. could it be because I'm using numbers, where you used names such as perry, so maybe I have to do things differently regarding syntax?
After creating & running the app scripts, how to trigger the sheets to update the data on each sheet when more data is added to a sales rep that is already listed without having to delete the old sheet?
If I copy and paste value in the new tabs as shown at 4:22 … Is there a way to update the data on the tabs created that will automatically update the master file ? Ex: I want to add a column for notes and if I update my notes in one tab how can I auto populate that data into the master?
Updates can only go in one direction. You can either have your original data in separate tabs and automatically generate the master or have your master data as a source and generate individual sheets. It won't work both ways.
good night. I'm trying to apply everything and the only problem I don't understand is that instead of copying the name of the person in the creation of the sheet, it assigns "salesRep" which would be the function or the taking of the name, right? It's all absolutely the same. Could you help me? Greetings from Argentina and from someone who does not understand English or script, until I found a very didactic person and you who are too! Thank you
Good Morning! It breaks my coconut and I had a tiny error. I was able to apply everything and I am also adding things that I learned in other places, thank you very much for your contribution!
How can I filter the broken out sheets/tabs, for instance... I want to use the data in the sales sheet to see who didn't make a sale on a work day. I have a reference sheet of days we worked, and I'm breaking out these individual sheets for each sales person. Can I compare the date of sale to my 'DaysWorked' tab to just see this sales persons' DaysWorked in which they made NO entry? I.E., Only see days on the DaysWorked reference sheet that do not match a sale for that worker on the SalesData sheet? anyone have a clue?
Please help me in this problem - I've created two html pages within the same project and I want to render page 1 with a text box and a submit button and when someone enters the value and press the button, it should open html page 2 with that variable showing here..I am able to open the page 2 but the value is not getting through, it shows undefined...
it would be great if you can share the app script link, or a copy this would help us save more time than typing entire script. rather, we could copy the script, and edit few fields
It runs to the point where it says [20-10-12 16:06:57:278 MDT] TypeError: ws.getRange(...).setformula is not a function at myFunction(Code:10:21) It creates a new page with a new student name but none of the data is pulled in as well. Help? Please.
how to automate google sheet file base on the name list. here are 60 people we want to create TA (Travel Authorization)for those who we selected from the list. the TA contains different information in different cells and we want to fill information in the TA Template only one time. one field (cell)(TA ID) is automatically generated ID. The TA, we need to do our staff every day for them to travel before they travel. thanks
Either OG or fellow watchers... okay last question before I go to bed... warning lesser level user (formulas = sure, script writing = holy crap) \\\\\\\\\\ Question / example: here you pull Columns \\\\\ 'sales data'!A2:F ////// over to new tab/page "Perry Neal". If I say... add a 'G' column in "Perry Neal", not "Sales data" and then add info to "Perry neal" "G" Column - the data is frozen in that cell, even if the A:F row data moves due to adjustments on "Sales data" tab/page. How can I attach the data from the new "G" column in "Perry Neal" to auto imported data from "Sales data"? (I like to be able to utilize filters alphabetically/dates etc) thank you very much in advance for your time. Love this channel. New subscriber.
i tried it does not work for me .. NVM i got it SUPER IMPORTANT - MUST have the same amount of rows and columns "Again, if you are filtering horizontally, you must assure that your source range and your criteria range contain the same number of columns."
Learn Google Spreadsheets I’m working on a file that needs to be sorted according to dates. I followed this video step by step. I replaced the “sales rep” with a date, unfortunately, it didn’t work out. I hope you help me with this. Thank you.
If you, or someone is willing to help, I keep getting this error: "The sheet name cannot be empty. (line 22, file "Tabs")" . I also get a new blank sheet every time I run it. Here is my script: function createSheets(){ const ss = SpreadsheetApp.getActiveSpreadsheet(); const sourceWS = ss.getSheetByName("1.DATA ENTRY"); const name = sourceWS .getRange(2, 2,sourceWS.getLastRow()-1,1) .getValues() .map(en => en[0]); const uniquename = [ ...new Set(name) ]; const currentSheetNames = ss.getSheets().map (s => s.getName()); let ws; uniquename.forEach(name => { if(!currentSheetNames.includes(name)){ ws = null; ws = ss.insertSheet(); ws.setName(name); ws.getRange("A2").setFormula(`=FILTER('1.DATA ENTRY'!A2:E,'1.DATA ENTRY'!B2:B="${name}")`); sourceWS.getRange("A1:F1").copyTo(ws.getRange("A1:F1")); } // if name doesn't exist }); // forEach loop through the list of names } // close createSheets function
Your video so create and I try using setFormula in the code but I always got the SyntaxError: missing ) after argument list (line 42, file"Code.gs") and the line 42 is: 42 ws.getRange("A4").setFormula('=FILTER('Roster copy'!A1:I43,'Roster copy'!A1:A43="${staffName}")'); Please help me correct it.
@@rawyabashir9804 yes, you can. just click on the arrow from the tab of the sheet, and chose *protect the sheet* , in the popup window, you can specify the cells or ranges to be edited by the editor. But before doing this, you need to share the link to the person with *right/access* for editing.
I would protect the header on the sheet. It won’t prevent them from working on it but will prevent them from changing the entire sheet at once such as the format.
Depending on your data you might be able to use Report Filter Pages in Pivot Table to get the separate tabs excelinexcel.in/ms-excel/formulas/show-report-filter-on-multiple-pages/
Hi friends someone can help me When I try to save project I am getting Syntax error.line 9, .map(sp => sp[0]); Where I was missing Please help me thank you
Besides the clarity of your explanations and examples, you show the strategies to figure out new ways to approach a problem. You are not afraid to try a technique that doesn't work at first, allowing us to see how to think through a bad result while seeking a viable solution. Yes, keep stretching the limits for us.
I rarely ever comment on instructional videos. But this is by far the best instructional video I have ever watched. I am familiar with some things, I know things can be done, just knowing which code to use is the hard part to figure it out. You actually described it all, and gave reasons for the code as you went along. I was able to split a 28,000 row tab, into 287 different tabs. The only issue I found was, a Google timeout of 6 minutes per execution of the script. I looked into work arounds to add to your code, but determined it was easier for me to just run this code about 12 different times to complete the split process. Kudos. Thanks.
my wife has now become very skeptical and thinks I'm having an affair because I spend so much time in my room. BUT i can´t get enough. THANK YOU (again!) for these great tutorials AND content.
I tried to get an array of results out of column values like you did in this video, but the way I did it is not as clean and simple as yours.
Thanks a lot. I always learn something from your tutorials.
I always follow this channels for Google Sheet stuff. Just amazing. Just a simple but effective solution. Love it.
You just saved me a headache from last night trying to use another formula thank you so much for this video
World's Best Teacher
Best Quality
Learn Easy and Understand
I Love And Like All Video
This video was amazing. You are really bringing smart solutions to our problems. I loved this video. Looking for such content & video in future. Waiting for next video. I appericate your work.
Great Lesson! Found out the Hard Way that an Apostrophe is not the same as a Reverse Tick!!
The one thing I did see was that Column Width should be Automated!!!
Awesome and is it gonna be triggered when we insert a new row in the main sheet? Thanks
Exactly what i was looking for... Thank you
This is amazing! Is there anyway to make something similar to this with dynamic data?
Perfect..!!! Learning a lot
I'm curious, I've tried Googling it, however how would you do this and create a new workbook instead? My use case is very similar, whereby I have three tabs: an FAQ/Introduction tab (no change/duplicate it in each workbook), a source data tab (almost exact challenge listed here), and a transformation tab (duplicate, only going to use importrange to copy down values).
Essentially, I need to create 60 different workbooks per "sales rep" versus 60 different sheets within the same workbook. Each workbook would have the same 3 tabs and only the source tab would be filtered down using the methodology outlined here. Edit: As an additional note - my company authorization does not permit me to use Google Collab, so just to preface, I can't use that solution.
Beneficial video learned a lot from this above video. This video is very useful in creating new sheets from the master sheet but I have one question how to recall newly generated sheets in more functions where we can put update data
Thanks this has been so helpful! I have a question: There is a data validation built into the last two columns of my sheets. Example: Choose A-C on dropdown. Is there anyway to keep the data validation from the master sheet and copy it to the rest of the sheets?
Thank you😭 Just what I was looking for and at the exact level of ease I needed.
Thank you for this well throught out instructional video. You did an amazing job of making this complex process seem approchable and atainable. is there any chance you have the code somewhere that can be copied? It would be very helpful not to have to type it all out from the screen.
I just get some new method again from you. Thanks Sir :)
Oh ya Sir. I want to show you my new spreadsheet, I made this to distinguish data from two columns
:
docs.google.com/spreadsheets/d/1XAi9gnVo4ONO-CGJ-RTRdO1ZDi0g48kHVAExfzrn0uE/edit?usp=sharing
Another awesome tutorial, thank you so much.!!
from 12:00 the important part
hi, i have 600 different value that I want to split into 600 different worksheet, since 1 google workbook maximum capacity is 5M cells, I'm affraid it wont't fit. Is it possible to split it into different workbook? thanks
Thanks so much for the video! Is there are way to delete the rows in the Sales Data master sheet once they are transferred to the new sheet? For example, I am trying to move a particular rows of clients from an "Active Job" sheet to a "Completed Jobs" sheet once once those clients status' in a drop down menu are updated to "Completed"
Amazing! Thank you! I've been looking for this a loooong time ago.
Nice. If you go back & make changes to the master sales data sheet, will the changes update in the individual sales people sheets or will you need to run the script again?
They will update.
Thank you for your explanation!
thanks a lot awsome content
Very cool!!! Thank you!!! ❤️
Awesome video. Instead of multiple worksheet can we do the same thing with multiple workbook. I mean split data from master to new workbook with sales person name if possible. I have to do the same thing but instead of worksheet i want to do it for different workbook.
How would you split a sheet into multiple sheets based on values from TWO columns? For example A:Brand and B:Model, or A:Region and B:Department.
Would you split sheets for each A value as demonstrated and then repeat the process using those A sheets to split into AB sheets? A demo video would be very helpful!
Please watch FILTER function video on the channel th-cam.com/users/LearnGoogleSpreadsheetssearch?query=FILTER%20function
Well explained!
Question: Can someone tell me how I auto populate new tables based on time? every month a new table, collecting all inputs from the source sheet. I assume i have to replace "const uniqueSalesPeople" section with a filter that looks at a timestamp at the source sheet. How do I formulate this
That’s awesome.. simply brilliant
👍👍
THANK YOU!
I ended up combining this with a few other scripts I find, modified, failed, watched this some more and came up with this code (below) Victory! It works and will save me and my team so much time. Thanks again!.
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Create Sheets')
.addItem('By Region', 'ByRegion')
.addItem('By District', 'ByDistrict')
.addToUi(); }
function ByRegion(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceWs = ss.getSheetByName("INDEX");
// 2 is second row 8 is column H where I have region names
const Region = sourceWs
.getRange (2,8,sourceWs.getLastRow()-1,1)
.getValues()
.map(ds => ds[0]);
const UniqueRegion = [...new Set(Region)];
const currentSheetNames = ss.getSheets().map (s => s.getName());
let ws
UniqueRegion.forEach(RegionName => {
if(!currentSheetNames.includes(RegionName)){
ws = null;
ws = ss.insertSheet ();
ws.setName (RegionName);
ws.getRange("A2").setFormula(`FILTER(INDEX!A2:H,INDEX!H2:H = "${RegionName}")`);
sourceWs.getRange("A1:H1").copyTo(ws.getRange("A1:H1"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
// Add Borders getRange(row, column, numRows, numColumns)
ws.getRange(1,1,ws.getLastRow(),ws.getLastColumn()).setBorder(true, true, true, true, true, true);
//Rezize Columns
ws.autoResizeColumns(1, ws.getLastColumn());
//Resize Picture Colums E 5th column and F 6th colum setColumnWidths(startColumn, numColumns, width) change the last number if you want wider
ws.setColumnWidths( 5, 2, 30);
} //if region name tab does not exist
}); // for each loop through the list of regions
} // close byRegion function
function ByDistrict(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceWs = ss.getSheetByName("INDEX");
// 2 is second row 7 is column G where I have district names
const District = sourceWs
.getRange (2,7,sourceWs.getLastRow()-1,1)
.getValues()
.map(ds => ds[0]);
const UniqueDistrict = [...new Set(District)];
const currentSheetNames = ss.getSheets().map (s => s.getName());
//ws is the newly created worksheet
let ws
UniqueDistrict.forEach(DistrictName => {
if(!currentSheetNames.includes(DistrictName)){
ws = null;
ws = ss.insertSheet ();
ws.setName (DistrictName);
ws.getRange("A2").setFormula(`FILTER(INDEX!A2:F,INDEX!G2:G = "${DistrictName}")`);
sourceWs.getRange("A1:F1").copyTo(ws.getRange("A1:F1"),)
// Add Borders getRange(row, column, numRows, numColumns)
ws.getRange(1,1,ws.getLastRow(),ws.getLastColumn()).setBorder(true, true, true, true, true, true);
//Rezize Columns
ws.autoResizeColumns(1, ws.getLastColumn());
//Resize Picture Colums E 5th column and F 6th colum setColumnWidths(startColumn, numColumns, width) change the last number if you want wider
ws.setColumnWidths( 5, 2, 30);
// Center Picture Colums E 5th column and F 6th getRange(row, column, numRows, numColumns)
ws.getRange(1,5,1,2).setHorizontalAlignment("center");
} //if district name tab does not exist
}); // for each loop through the list of districts
} // close createSheets function
Thank you! Great job...very fine!
Really great! One question, can the agent update the new sheet, at the same time thr original is also updated?
You can’t update anything under the filter function unless you update it from the original data sheet. I would filter it by date on the original and find it that way.
@@BustaCap i see. I think what i need is distribution of rows to each sales rep from a master file to a separate sheet they can update and see changes in master.
Super thanks for this!
Wonderful video. How could we automatically arrange sheets in alphabetical or numerical order?
LOVE this tutorial! How can I get it to pull from multiple worksheets based on the same value in both? My code is: =filter('K-12 Intake Response Form'!C2:AJ,'K-12 Intake Response Form'!A2:A="HE").............but I want it to also pull from my '2020 Y5s' using the same "HE" column value.
Thanks for very useful code. Pls also update will it work if update more data.
This saves my life!!
:)
Super... How to get these practice spreadsheet
Good question
How to combine multiple sheet into single sheet in spreadsheet? Is it possible to make it?
Very great video !
One question : I have tried to update data on a tab but when I do it, all the data from the tab dissapear.
Is there a way to update the data on the tabs created that automatically update the master file ? In other words,how to ensure that data update work in both ways ?
You can't update both ways with formulas.
thankyou
Great! Thank you! Is it possible to separate values in lines with the delimiter ";"?
th-cam.com/video/_RZYr8127fo/w-d-xo.html
Awesome!!
can you also make a video on Power BI?
Your’e awesome man! Thanks alot!!!
Thank you so much!
I've already tried it and its working. However, what if I'll insert new name of salesperson on the data? I've tried the existing name in salesperson and data will be automatically copy to its corresponding sheet. However, when a new name is inserted, there will be no sheet to be added corresponding to a new name. What should I do for this situation?
Thank you so much for the help!
My first guess would be go back and run the script again, as you did the first time to get it to work. It will skip all the ones that are created, and only create new tabs for the newly added salespeople since the last time you ran the script.
If you change a value in the Master sheet will it automatically update the corresponding subtab or do you need to run the function again to update?
It will automatically update.
@@ExcelGoogleSheets How do I set a trigger to update the subtabs only at a certain date (every Friday) even when changes are made to the master daily? Thanks!
Every time I run the script to split data into separate sheets I see that the new tab is initially numbered before it takes the text label. Will there be a performance hit the 1000th time I run this and we're on tab 5000? Is there a script to reset the tab numbers count when everyone has closed the sheet at the end of the day?
Thanks for the video! There is a blank value for my "uniqueSalesPeople". How to remove that? Also when I am running the app again the scripts stops first with an error saying the sheet name already exists. If a new salesperson is added I am not able to add the sheets using the script as it stops at the first iteration.
Great Video. Where can I get the code?
how do i edit this formula so that it also formats the cells to match my original sheet, including merged cells?
How would you create a sheet that combines/group two or more sales rep?
Hello, I have followed your tutorial line by line and my code identical to yours, but it keep giving me exception error on line 22, ws.setName();, saying a worksheet with that name already exists? I know its supposed to loop and not create any worksheet that is already in place and just update the data, but its not doing that and stops at this error. Please help. Thanks for your helpful tutorial btw!
ps. could it be because I'm using numbers, where you used names such as perry, so maybe I have to do things differently regarding syntax?
After creating & running the app scripts, how to trigger the sheets to update the data on each sheet when more data is added to a sales rep that is already listed without having to delete the old sheet?
It happens automatically, no need to run anything.
If I copy and paste value in the new tabs as shown at 4:22 … Is there a way to update the data on the tabs created that will automatically update the master file ?
Ex: I want to add a column for notes and if I update my notes in one tab how can I auto populate that data into the master?
Updates can only go in one direction. You can either have your original data in separate tabs and automatically generate the master or have your master data as a source and generate individual sheets. It won't work both ways.
please help!i am facing error. it says ref error
Can you show me how to make Spreadsheet to create a new sheet everyday (named by date) automatically?
good night. I'm trying to apply everything and the only problem I don't understand is that instead of copying the name of the person in the creation of the sheet, it assigns "salesRep" which would be the function or the taking of the name, right? It's all absolutely the same. Could you help me? Greetings from Argentina and from someone who does not understand English or script, until I found a very didactic person and you who are too! Thank you
Good Morning! It breaks my coconut and I had a tiny error. I was able to apply everything and I am also adding things that I learned in other places, thank you very much for your contribution!
Awesome!
Hello, I am following your Video step by step but I end up with an Error stating no matches are found in Filter evaluation suggestions?
How can we get just specific columns, instead of a range. Like. A1:A then C1:C?
{A1:A,C1:C}
how would I just break out the dates from a list and put the month of entries in separate sheets
how to automate creat new sheet if a sale rep get a new distinct value
How collect data in the range to one column as array ? Thanks
How can I filter the broken out sheets/tabs, for instance... I want to use the data in the sales sheet to see who didn't make a sale on a work day. I have a reference sheet of days we worked, and I'm breaking out these individual sheets for each sales person. Can I compare the date of sale to my 'DaysWorked' tab to just see this sales persons' DaysWorked in which they made NO entry? I.E., Only see days on the DaysWorked reference sheet that do not match a sale for that worker on the SalesData sheet? anyone have a clue?
Thanks for sharing the video. While trying i am getting an error -sourceWS.getrange is not a function. Please help
Please help me in this problem - I've created two html pages within the same project and I want to render page 1 with a text box and a submit button and when someone enters the value and press the button, it should open html page 2 with that variable showing here..I am able to open the page 2 but the value is not getting through, it shows undefined...
Check my CRUD series for a solution.
how can I save data automatically from master sheet to multiple sheets in daily updated data in
Can this be done on excel?
it would be great if you can share the app script link, or a copy
this would help us save more time than typing entire script.
rather, we could copy the script, and edit few fields
It runs to the point where it says [20-10-12 16:06:57:278 MDT] TypeError: ws.getRange(...).setformula is not a function
at myFunction(Code:10:21)
It creates a new page with a new student name but none of the data is pulled in as well. Help? Please.
what if i want to split into sheets per region?
how to automate google sheet file base on the name list. here are 60 people we want to create TA (Travel Authorization)for those who we selected from the list. the TA contains different information in different cells and we want to fill information in the TA Template only one time. one field (cell)(TA ID) is automatically generated ID. The TA, we need to do our staff every day for them to travel before they travel. thanks
MY SAVIOUR!
Either OG or fellow watchers... okay last question before I go to bed... warning lesser level user (formulas = sure, script writing = holy crap) \\\\\\\\\\ Question / example: here you pull Columns \\\\\ 'sales data'!A2:F ////// over to new tab/page "Perry Neal". If I say... add a 'G' column in "Perry Neal", not "Sales data" and then add info to "Perry neal" "G" Column - the data is frozen in that cell, even if the A:F row data moves due to adjustments on "Sales data" tab/page. How can I attach the data from the new "G" column in "Perry Neal" to auto imported data from "Sales data"? (I like to be able to utilize filters alphabetically/dates etc) thank you very much in advance for your time. Love this channel. New subscriber.
i tried it does not work for me .. NVM i got it SUPER IMPORTANT - MUST have the same amount of rows and columns "Again, if you are filtering horizontally, you must assure that your source range and your criteria range contain the same number of columns."
Can you use the date as a filter to create new tabs?
it's possible, but more programming will be necessary
Learn Google Spreadsheets I’m working on a file that needs to be sorted according to dates. I followed this video step by step. I replaced the “sales rep” with a date, unfortunately, it didn’t work out. I hope you help me with this. Thank you.
I'm getting this error 6:59:43 PM Error
TypeError: ss.getSheetByName is not a function
Thanks, can we get the code to copy it?
Split one sheet into multiple sheets every X row. Work with Excel and Google Sheet, how ?
Syntax error: SyntaxError: Identifier 'sourcews' has already been declared line: 6 file: excel.gs
any idea?
says I have a syntax error on line 7 and I have everything like you have it... what could I be doing wrong?
check your geo syntax th-cam.com/video/ljvB2arCsNQ/w-d-xo.html
If you, or someone is willing to help, I keep getting this error: "The sheet name cannot be empty. (line 22, file "Tabs")" . I also get a new blank sheet every time I run it.
Here is my script:
function createSheets(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sourceWS = ss.getSheetByName("1.DATA ENTRY");
const name = sourceWS
.getRange(2, 2,sourceWS.getLastRow()-1,1)
.getValues()
.map(en => en[0]);
const uniquename = [ ...new Set(name) ];
const currentSheetNames = ss.getSheets().map (s => s.getName());
let ws;
uniquename.forEach(name => {
if(!currentSheetNames.includes(name)){
ws = null;
ws = ss.insertSheet();
ws.setName(name);
ws.getRange("A2").setFormula(`=FILTER('1.DATA ENTRY'!A2:E,'1.DATA ENTRY'!B2:B="${name}")`);
sourceWS.getRange("A1:F1").copyTo(ws.getRange("A1:F1"));
} // if name doesn't exist
}); // forEach loop through the list of names
} // close createSheets function
Any ideas?
It seems like you have some blank values in that column and that's causing the issue. You can't make a sheet with no name.
Important thing! if you use FILTER now you don't use "," between arguments, instead you use ";"
That's not necessarily correct. Please watch this for clarification th-cam.com/video/ljvB2arCsNQ/w-d-xo.html
How to create this if i already have the template tab
Like in one video {{name}} ..
One like not enough for you man
Thanks!
Can you paste the final code please
i keep getting this error, "Exception: The sheet name cannot be empty. line 22" can anyone help?
Your video so create and I try using setFormula in the code but I always got the
SyntaxError: missing ) after argument list (line 42, file"Code.gs")
and the line 42 is:
42 ws.getRange("A4").setFormula('=FILTER('Roster copy'!A1:I43,'Roster copy'!A1:A43="${staffName}")');
Please help me correct it.
use back ticks for setFormula(``), NOT '.
@@ExcelGoogleSheets Thank you, it no more show error! :)
can u share the dataset?
Kindly send the code
How I can prevent others to change the format of sheets text or color...
protect the sheet, and you can set some ranges or cells to editable for regular users
@@netboy1102 I need the editor just entering data and I want to prevent change the format by the editor
@@rawyabashir9804 yes, you can. just click on the arrow from the tab of the sheet, and chose *protect the sheet* , in the popup window, you can specify the cells or ranges to be edited by the editor. But before doing this, you need to share the link to the person with *right/access* for editing.
I would protect the header on the sheet. It won’t prevent them from working on it but will prevent them from changing the entire sheet at once such as the format.
Am I the only one who gets error "sp is not defined" from the line 9 (".map(sp...?
I TRIED THE FILTER BUT ITS NOT WORKING, ONLY FILTERXML
HELP
why not give a link to the code for us lazy people lol. nice tutorial btw :).
I cannot use filter formula in my excel :')
FILTER is available only in recent Excel versions, so if you're not on Office 365 then it's not likely to be available.
Depending on your data you might be able to use Report Filter Pages in Pivot Table to get the separate tabs excelinexcel.in/ms-excel/formulas/show-report-filter-on-multiple-pages/
Hi friends someone can help me
When I try to save project
I am getting Syntax error.line 9,
.map(sp => sp[0]);
Where I was missing
Please help me thank you
You are probably still under Rhino instead of V8. Either switch to v8 runtime or use old javascript syntax.
@@ExcelGoogleSheets thank you very much sir your teaching and support
@@ExcelGoogleSheets Thanks a lot sir it running perfectly once again thank you sir
Hi all please help me to get over this error am facing same .map is not a function error pls help what to use there ...
@balachander madhaiyan pls help