- Видео 102
- Просмотров 659 938
Spencer Farris
Добавлен 23 дек 2011
Google Apps Script: Take Action Based On Formatting
How to take an action (copy, move, email, PDF...) based on formatting (strikethrough, bold, italic, color...)
Sheet (Make a copy from the File menu)
docs.google.com/spreadsheets/d/19NxJbL76M0g8E0UGxG-QvpPabr0jNHzdFQkIBRWoCV4/edit?gid=0
Script:
function myFunction(){
const sh = SpreadsheetApp.getActive();
const ss = sh.getSheetByName("First");
const sOut = sh.getSheetByName("Out");
const r = ss.getDataRange();
const rValues = r.getValues();
const rFormats = r.getRichTextValues();
let out = [];
for (let i in rFormats){
if (rFormats[i][0].getTextStyle().isStrikethrough())
out.push(rValues[i]);
}
sOut.getRange(sOut.getLastRow()+1,1,out.length,3).setValues(out);
}
I use AppsScript Color for the dark...
Sheet (Make a copy from the File menu)
docs.google.com/spreadsheets/d/19NxJbL76M0g8E0UGxG-QvpPabr0jNHzdFQkIBRWoCV4/edit?gid=0
Script:
function myFunction(){
const sh = SpreadsheetApp.getActive();
const ss = sh.getSheetByName("First");
const sOut = sh.getSheetByName("Out");
const r = ss.getDataRange();
const rValues = r.getValues();
const rFormats = r.getRichTextValues();
let out = [];
for (let i in rFormats){
if (rFormats[i][0].getTextStyle().isStrikethrough())
out.push(rValues[i]);
}
sOut.getRange(sOut.getLastRow()+1,1,out.length,3).setValues(out);
}
I use AppsScript Color for the dark...
Просмотров: 104
Видео
Google Sheets: Bold Different Words in a Cell with Apps Script and Retain Existing Formatting
Просмотров 4342 месяца назад
How to bold (or apply other formatting) to a cell without losing existing formatting Inspired by this question on the Product Forums support.google.com/docs/thread/288793033/sheets-bold-specific-words-within-cell-using-apps-script Sheet (Make a copy from the File menu) docs.google.com/spreadsheets/d/1xUk0r3LCmFxvoVJ1ah2UzQNcutba5OwlQ38adc6sbdk/edit?gid=0#gid=0 Script function boldStuff() { cons...
DEPRECIATED - Google Sheets: Bold Different Words and Retain Existing Formatting
Просмотров 2282 месяца назад
DEPRECTIATED Use ruclips.net/video/TBRmpWbFNvs/видео.html
Google Sheets / Apps Script - Move Data Down with Dropdown
Просмотров 3553 месяца назад
How to move data down to the last row with a dropdown selection Sheet (make a copy from the File menu) docs.google.com/spreadsheets/d/1OD601_yrkCo0-kifKD3gToF5FUqHy_kMK1w8zrL3cmg/edit?gid=0 Script function onEdit(e){ if (!e) throw "Do not run from Editor"; moveDataDown(e); } function moveDataDown(e){ const r = e.range; if (r.rowStart 1 || r.columnStart != 3) return; const src = e.source.getActi...
Conditionally Insert Checkboxes
Просмотров 4546 месяцев назад
How to insert checkboxes when a condition is met from manual data Sheet (make a copy from the File menu) docs.google.com/spreadsheets/d/10ys1NPTPgGXvn7gEwLpt2iHoJ8qcMyT7u31mZwdjECA/edit?usp=sharing Script function onEdit(e){ if (!e) throw "Do not run from Editor"; insertFollowupCheckbox(e); } function insertFollowupCheckbox(e){ const r = e.range; if (r.columnStart != 2 || e.value greater_than_s...
Sort Sheets
Просмотров 6419 месяцев назад
How to sort the sheets in a spreadsheet alphabetically AND exclude a sheet from the sort Notes * You can exclude additional sheets by increasing the .splice() number and the index in the for () loop Spreadsheet (Make a copy from the File menu) docs.google.com/spreadsheets/d/1yrH_HvGlTdDd7s7WEKiGXCWR5H9Dn4UEiVAlLtniaVQ/edit Script (Replace less_than_sign and greater_than_sign appropriately) func...
Conditional Formatting Across 2 Sheets
Просмотров 13 тыс.Год назад
Conditional Formatting Across 2 Sheets
Google Sheets: Delayed Trigger After Event
Просмотров 1,7 тыс.Год назад
Google Sheets: Delayed Trigger After Event
Manipulate Rich Text Data - Extract Bold Text
Просмотров 886Год назад
Manipulate Rich Text Data - Extract Bold Text
Use Checkboxes to Increment / Decrement over a Range
Просмотров 3 тыс.Год назад
Use Checkboxes to Increment / Decrement over a Range
Make a Button to Increment / Decrement a Value
Просмотров 24 тыс.Год назад
Make a Button to Increment / Decrement a Value
Google Sheets: Get Random Value From List
Просмотров 2,4 тыс.Год назад
Google Sheets: Get Random Value From List
Google Sheets Dropdown from List / CSV
Просмотров 2,9 тыс.Год назад
Google Sheets Dropdown from List / CSV
Automatically Replace Value When Entered
Просмотров 2 тыс.Год назад
Automatically Replace Value When Entered
Update Child Sheet Formulas When Parent Updated
Просмотров 868Год назад
Update Child Sheet Formulas When Parent Updated
Copy Values, Not Formulas / Archiving Data
Просмотров 1 тыс.2 года назад
Copy Values, Not Formulas / Archiving Data
Google Sheets: Hide Formulas with IMPORTRANGE
Просмотров 12 тыс.2 года назад
Google Sheets: Hide Formulas with IMPORTRANGE
Connect to Calendar - Create Events From Spreadsheet
Просмотров 3,7 тыс.2 года назад
Connect to Calendar - Create Events From Spreadsheet
Hi Spencer, very well explained and detailed instructions, thank you very much! Google sheets don't have option to input special characters, I was wondering about a script for find and replace. My idea is to have "gl_a" as abbreviation for alfa, which is CHAR(945), but have no idea how to make that work, any help is highly appreciated!
This is amazing and also way over my head so I hope you can help me in modifying it :) If I wish to create a new sheet but only with 1 variable, how should I do that? I created a Bio sheet and a Template sheet. I wish that whenever I add a new member's name into the Bio sheet, it triggers a new sheet (a duplication of the template sheet) with this memeber's name. How do I do that? Thank you!
What a great video! I was wondering if a can create recurring events to be displayed in Google Sheets. I created a interactive calendar on google sheets but couldnt find a way to create recurring events to link to this calendar besides listing all the events or input manually this info
In scripting it's called an Event Series developers.google.com/apps-script/reference/calendar/calendar-app#createEventSeries(String,Date,Date,EventRecurrence)
Thank you, this is so helpful!
Hello, im trying to get this to get this to work but not having any succes, i copied and pasted the text into the script box
After saving it should just /work/
absolutely amazing. can i use this in single spread sheet on multiple sheets. Example. Spreadsheet1 has 2 sheets sheeta and sheetb sheeta i want to lock row after i edit the 10th column sheetb I want to lock row after i edit the 20th column how do i differentitate
Sure, you would just put an IF for the sheet name and then an IF within for the column
@@SpencerFarris Thank you for the swift response. I'm not that good with coding can you just give me the code. I will edit the sheet names and column numbers.
@@evergreentravelsvizag function ____(){ const sheet = e.source.getActiveSheet(); const r = e.range; if (sheet.getName() == "sheeta" && r.columnStart != 10 || sheet.getName() == "sheetb" && r.columnStart != 20) return; ... ... }
@@SpencerFarris function lockRow(e){ const sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const r = e.range; if (sheet.getName() == "Logs" && r.columnStart != 44 || sheet.getName() == "Serial" && r.columnStart != 8) return; let protection = sh.getRange(e.range.rowStart,1,1,sh.getMaxColumns()).protect(); protection.removeEditors(protection.getEditors()); if (protection.canDomainEdit()) { protection.setDomainEdit(false); } } using this code and checked in executions the code is failing
Super useful!!!
Thanks!
How do I run this in the copy I made? It's telling me I need Authorization and to verify some app, but I don't know what app.
It's asking for authorization / verification of the script. The script runs as you, or the triggering user, so it's just asking you to say "yes, I authorize you to run as me"
@SpencerFarris I hope you see this. Is there a way to contact you? I'm trying to do this, for the first time for my company and very much need help. I found the script app to copy and paste the script into, but I'm not sure how to make it work on the actual Google sheet. I'd very much like to gain your support!
spencer.farris@gmail.com
What do I need to change to execute this across all the sheets in a workbook?
Get rid of src.getName() != "Checkbox" in the IF statement
Hello, great video, friend. Thank you. I want to ask if there is any chance of inserting and extracting rich text from within the Google Sheet? For example, I did a CRUD project, where I render the HTML separately. In this project, the admin user can insert data so that other users can view this data in another HTML interface. However, when the admin inserts a link or something styled like bold or italic, this will be saved to the Google Sheet but without the link or bold or italic. And when another user searches for this subject, the link does not appear, nor do other styles that were inserted in the HTML text box through the admin interface. Would it be possible to make the Google Script send and bring styled texts from a Google Sheet? Thank you for the help
Hello! The script is running fine! But what if I want it to look only at the last row from column A to paste the data? I have in other columns more stuff and its writing the script on the very last row from all cells. What can I do? Thank you!!
I know there are other ways, but here's one ruclips.net/video/une5OolKsXI/видео.html
@@SpencerFarris Thank you!!
Good 👍
Hello, I am using this to keep track of deliveries. I have 3 locations and start filtering date from Row 10, Colum A. I want everything to move when "Delivered" is selected... How would this formula look? I am not a computer person, I need help!
Have you tried modifying the script at all? Everything you need should be there with some use-case modifications.
I made a video to explain it my way. ruclips.net/video/2KzhjdcewQc/видео.html
Just click on extentions then app scripts then delete everything then copy and paste script below. BUT! this script is programmed to have the checkboxes in column A. So if you have your checkboxes in column C, then change the 1 to 3. If your checkboxes are in column F then change 1 to a 6. function onEdit(e){ if (e.range.columnStart != 1 || e.value != "TRUE") return; SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart); }
script if your checkboxes are in column B function onEdit(e){ if (e.range.columnStart != 2 || e.value != "TRUE") return; SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart); }
Hey spencer I'm trying to do the same concept apart from I'm trying to lock a column 24 hours after editing it. tried to change the script to do this but im stuck, can you please help
spencer.farris@gmail.com
I thought you said the date wouldn't go away if you unchecked the box but it appears you were able to clear it by unchecking the box.
Do you have a timestamp?
Can you lock specific cells? If a specific cell changes to a trigger value it automatically locks and can only be changed again by the sheet owner? I need the script to look at specific columns and any cell in those columns that get changed to "PAID" get locked not the entire row or column.
Sure, just combine the logic with my other video about locking on edit
Hi, I have a sheet with more than just 3 columns. Would I just edit src.getRange(r.rowStart,1,1,3).moveTo(dest.getRange(dest.getLastRow()+1,1,1,3)); to something like src.getRange(r.rowStart,1,1,11).moveTo(dest.getRange(dest.getLastRow()+1,1,1,11)); if I have 11 columns?
Yes!
0:57 0:58 374641579 9
Hii sir
hello
this is kinda exactly what i needed but missing the important part, i want those 2 buttons but they shoudnt only work on the A2 Box, it should increase or decrease the box i highlighted/clicked before. how would i do that?
ah found it^^ its sheet.getActiveCell() works perfekt^^ thanks for the vid
You would use <sheet>.getActiveRange() as the thing to increment/decrement
Thank you so much for making this video! This function is exactly what I need for a school project. Unfortunately, the code doesn't seem to be working for me. I opened a new sheet, opened the apps script, pasted the code from shane0163's comment, went back to the original sheet, typed "o1," and it didn't replace it. This was after trying to follow the tutorial and experiencing the same issue after the test at the 3 minute mark, where it just didn't replace it. Do you have any idea what's going on?
can you share the spreadsheet with me? spencer.farris@gmail.com
@@SpencerFarris Unfortunately, I no longer have access to the blank sheet that I tested it in, and when I tried shane's code in a new blank sheet, it worked as expected, but my alternate solution does work (I just have to type the prices in manually instead of the names). I will be saving this video for if I ever need it, so thank you for making it!
@@Caleb_3D Glad you got it sorted :)
Subbed! One question, say you have formatting applied on the second sheet like fill colors. Is there a way to match the formatting from the second sheet into the first? For example, if Rant Kant was filled yellow and Elzar Mann was filled green and I wanted that to apply to the first page as well?
I don't follow the ask, sorry?
Hi, how would you change the formula if you needed more than one criteria? For example it needed to match data on column B as well.
Use the AND() function and put the other condition
thanks a lot, this was very useful; I've tried replicate this code to perform other action but replacing 1 to 0,01 (to use as percent) and I've get an error message, so… it's possible to use 0,01?
What error did you get? You may need to check the locale settings OR use .01 instead of ,01
update plssss 2024
WDYM?
Add editor: ruclips.net/user/clipUgkxZ3R4qWS62Oh8trl3hojcGQ03SUyg1433?si=GPwAoprZE14sIJCV
Can I use this script to run when a bar code is scanned
Can you do COUNTIF functions on a 3D Range? (e.g. count how many times a word appears across the same cell range in multiple sheets; I have ~30 sheets so rewriting the COUNTIF function for each sheet with 30 "+"s takes forever)
I'm sure something could be programmed, but I don't have a script pre-made for that Alternatively use QUERY with a range of sheet names
This is perfect! I am getting a document to save but it is not saving as a PDF I get a document that is nonsense. Any help would be greatly appreciated.
Not sure. Want to share it with me?
@@SpencerFarris Can I email you?
@@SpencerFarris Thank you so much for responding back to me. I sent you an email with the shard requirements.
How to enable dark mode in apps script editor?
I use the AppsScript Color add on
Love your work mate. Ken oath
got the code to work, but what if I wanted to increment a value on another sheet? what part of the code do I have to edit to increment in a different cell on a different sheet?
This is amazing. Trying to adapt it to run with multiple spreadsheets (to prevent Mary or John from seeing each others work, or the database) but running into problems. Any advice?
I believe you can do this with the copy() method of RichTextValue, no need for getRuns etc. (?)
Hi Spencer! How can I make this formula apply to multiple column strings?
Solid tutorial. I've spent an hour looking for this 5 minute solution.
Superb tutorial! What about comparing 3 sheets for duplicates? is it possible to use this formula?
Thank you, @Spencer Farris! I see this is from 2021 yet still today very much relevant - and I have a ChatGPT+ subscription yet still couldn't hack this simple thing. You rock my man🤘🏻
Neat tutorial!
Hello i wnat o ask you a help. i want to lock the row entre entering a specifing value like " manager" then that row is locked only the person who has the access can edit in case there is mistake during the entring process. please can you help i will be happy. i alredy sent you a message in your email. Thank you
Could you help me I'm having problems? I could send you over my test spreadsheet.
Is there a way to lock drop downs with this?
This locks any edits
Low sound ;(
Darn, it sounded fine when I recorded it. It may have lost audio quality when I moved it through the other programs. I'll probably have to re-record. Thanks for letting me know.
I made a new one. Hopefully the audio comes across better ruclips.net/video/TBRmpWbFNvs/видео.html
Yesss = best!
Hey there! I am using your script to get checkboxes to increment and decrement, and it works great, but I can not figure out how to get multiple groups to calculate. Please help! Columns C (+) and D (-) add and subtract Column B total. How do I add columns E, F, and G to the script? I can not figure out how to also get F and G to add and subtract Column E's Total... Script I am using... function onEdit(e){ if (!e) throw "Do not run from editor"; incrementAndDecrement(e); } function incrementAndDecrement(e){ const src = e.source.getActiveSheet(); const r = e.range; if (src.getName() != "Individual Student/Family Services" || r.columnStart < 3 || r.columnStart > 4) return; let out; if (r.columnStart == 3){ out = r.offset(0,-1); out.setValue(out.getValue()+1) } else { out = r.offset(0,-2); out.setValue(out.getValue()-1) } r.setValue("FALSE"); }
Hi Spencer, great video. I am running a similar script but having issues when others are checking multiple boxes at the same time. The script ends up moving the wrong row and it seems the only way around it is checking the box one at a time. We have multiple people using the sheet and I was hoping to create a fail safe to avoid this issue. If you have any suggestions, that would be great!
Did you make an installed trigger along with the simple trigger?
Hi, not sure why but when i copied the code and only changed the location of the column for the drop down it takes the selected row and then the last row also. The only change was for the column which for me is 9 instead of 3
Also a follow up, my data is coming from a google sheet is there a reason that the coding works if i manually type in something but if its the data from the form it does not move to the other tabs?
@@Crumbsism Interesting. Can you share the spreadsheet with me?