Is there a way to connect a server calculator sheet to a client one with only importrange ? The question is: if the user wants to customize some values (inser own variable values for example), is there a way to not to collect in the server spread the users live datas changes that are calculated in server? The server: has the calculation logics and formulas that sends result to the client. The server also has the inputed users datas to manage calculation but i don't wanna collect that....if i want to give to a customer that client spread I would that the client insert own values, receive realtime calculation from server without sending to the server that datas.... But only get back the formula string to apply it in the client spread...

hey think i could have some help with this please ?

i want to increment/decrement F by E on 40 different row. i've tried some things but i cant get it to work, i'm pretty sure i can do with less coding than 2 button for each row. i dont mind if every row increment at the same time if i can reset all E row to 0. to make it more concrete, i want to have an initiative sheet for dnd, and on this sheet i want a column with damage/healing received as value (E), a second column beeing the cumulative damage (F), after its easy i have a total hp column (G) and a dead or alive/remaining hp (H) which use a if/else function. i can get the script to work on a button, for each row, but since i have to draw the button for each and its making different size button my ocd are cranking it up XD (if you could help thanks)

Is there a way to add an auto time and date in the first row when it moves??

I love it thank you so much !!

Hi Spencer, back again but different problem same concept. I have a workbook that has a status in column AH. There is a total of 11 status values but only 2 worksheets. I took from this your script and amended where I thought it needed to be but they doesn't seem to be working? function onEdit(e){ let r = e.range; if (r.columnStart != 34 || r.rowStart == 1 || e.value == null) return; const sh = SpreadsheetApp.getActive(); const valArray = ["ADOPTED","ADOPTED_$","CFM:PAID NOTICE","AVAILABLE","NOT AVAIL","DECEASED","BAD DEBT","PERM FOSTER","TO BE RETURNED","PALLIATIVE","FINALISED"]; const destArray = ["In_Care","In_Care","In_Care","In_Care","In_Care","Finalised","In_Care","In_Care","In_Care","In_Care","Finalised"]; let dest = sh.getSheetByName(destArray[valArray.indexOf(e.value)]); let src = sh.getActiveSheet(); if (dest.getName() == src.getName()) return; src.getRange(r.rowStart,1,1,34).moveTo(dest.getRange(dest.getLastRow()+1,1,1,34)); src.deleteRow(r.rowStart); The 'In_Care' sheet is the main sheet and I only want to move the row if the status changes to FINALISED or DECEASED to the 'Finalised' sheet. Can you see what I am doing wrong?

it has the lock icon. but editors can still edit on the sheet. it is not locked. even if i change the permission on the original file to "you only", it gives all the editors edit permission on the copy.

it's not working

Hi Spencer, I'm not sure what I'm doing wrong.. This is the formula I pasted in AppScript function onEdit(e){ if (e.range.columnSmart !=6 || e.value != "TRUE") return; SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart); } The column with my checkboxes is F I saved it but when I check off a box on my google sheet it doesn't hide it. Is there something I'm missing? I'm very green when it comes to coding and AppScript TY

You're the boss

One thing missing, if the value changes from original, then we have to remove the Check boxes, how would we do that

thanks, exactly what i needed. now after a sheet has been copied with protections, how do i rename the copied sheet to something other than the number given?

Hi, I tried the solution it only works if I manually click the checkbox. I wanted to implement an IF Function on other rows such that if I checked one box it should give TRUE/FALSE on other rows as well. in turn I wanted to create a select and deselect kind of funtion on my google sheet. But it does not work over there. Kindly help here.

Hi, can we use this for duplicate sheet ?

How do I deploy this step by step on my google sheet? Is it through Tools>AppScript?

it worked, thank you! if i only wanted it to work within one sheet though, how would i do that? i have sheet1 and sheet2 but i only want the script to apply to sheet1 and not sheet2.

This is very useful, thanks! Is there a way to run the update that doesn’t blank out the screen and the data in it for that brief second or two?

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

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

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

Super useful!!!

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.

@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!

What do I need to change to execute this across all the sheets in a workbook?

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!!

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!

I made a video to explain it my way. kzbin.info/www/bejne/aHzdmZ2amMqqh8U

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); }

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

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.

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.

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?

0:57 0:58 374641579 9

Hii sir

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?

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?

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?

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.

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?

update plssss 2024

Add editor: kzbin.infoUgkxZ3R4qWS62Oh8trl3hojcGQ03SUyg1433?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)

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.

How to enable dark mode in apps script editor?