I am trying to create a script to protect my formulas in sheets that co-workers will use. I have 10 workbooks with 26 sheets each, all are virtually identical as far as the the locations of the protected and unprotected ranges.   As far as I can tell the best way to do this is to protect the whole sheet then unprotect certain cells for data entry.   This is the script I have so far, I've cut out about 90% of the ranges I need unprotected to make it smaller.  function ProtectRanges() {   var sheet = SpreadsheetApp.getActiveSheet();   var protection = sheet.protect().setDescription("Protect Formulas");   var unprotected = sheet.getRange("S6:AF51")   sheet.getRange("S66:AF66")   sheet.getRange("S70:AF70")   sheet.getRange("S72:AF72")   var me = Session.getEffectiveUser();   protection.addEditor(me);   protection.removeEditors(protection.getEditors());   if (protection.canDomainEdit()) {     protection.setDomainEdit(false);   }  }   The problem I'm having is that the people that I share this with can't edit any of the cells, protected or not. I need to be the only one who can edit the formulas, but the data entry cells need to be open for them to edit.
122624
I have created 50 spreadsheets for 50 students. Each spreadsheet contains 33 sheets, numbered 1 to 33, along with a couple of other sheets.  Within each of these 33 sheets, there are many protected ranges.  I accidentally protected a range that ought not to have been protected. So, with 50 students each having 33 sheets, I have over 1800 sheets to unprotected.  I want to loop through each sheet, 1 to 33, and remove protection from cell F15. Right now, I (the owner) am the only editor.  Since I'm not an actual programmer, I've clearly not done this properly.  Can someone offer a suggestion of how I can change my Google Script to make this work?  var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets();  for (var i=2; i  &lt;sheets.length; i++) {   var sheet = ss.getSheets()[i];   sheet.activate();   SpreadsheetApp.flush();   var range = sheet.getRange('F15');        var protection = range.protect().setDescription('Protected range');        var me = Session.getEffectiveUser();        protection.addEditor(me);        protection.removeEditors(protection.getEditors());        if (protection.canDomainEdit()) {          protection.remove();          } //end of if statement  }
66459
I'm doing a spreadsheet for an event and I need to split a row into multiple rows. It's getting data from a form.  I need a script that can arrange the data like in the blue rectangle.
67991
I have to do work between two spreadsheets and I need to view them side by side. I can't just open the workbooks in separate instances because I need to make use of features like paste formatting, and all of that goes away when the workbooks aren't in the same instance.  Is there anything I can do to get two windows open, each with a separate workbook, each maximized on a different monitor, and still have access to the advanced cut/paste features?
101809
I'm using Google Sheets to keep track of inventory. As the year goes on, the sheet gets quite long, so it is a pain to scroll all of the way to the bottom each time we start a new day, end the day or make a shift change.  Is there a way to add a script that will automatically scroll to the next available cell in column A when you open the sheet?  The closest thing I could find was this, but it goes to the bottom of the entire sheet, which is no good because we have extra rows on our sheet:  function onOpen() {   var sheet = SpreadsheetApp.getActiveSpreadsheet();   var entries = [{name:"MyFunction", functionName:"myFunction"}];   sheet.addMenu("Scripts", entries);   myFunction(); };  function myFunction() {   var ss = SpreadsheetApp.getActiveSpreadsheet();   var mysheet = ss.getActiveSheet();   var lastrow = mysheet.getLastRow();   mysheet.setActiveCell(mysheet.getDataRange().offset(lastrow-1, 0, 1, 1)); };
35150
I have the following annoying problem with Excel 2010. I use Conditional formatting to apply bold formatting to each line of the spreadsheet that meets a condition expressed by a formula. In the "applies to", I write =$A$2:$Q$149, which is the current extent of my spreadsheet. But I want this range to grow with my spreadsheet, i.e., I want the formatting to permanently apply to the whole spreadsheet. Regularly, my "applies to" field is transformed into a complex range, that selects most lines of spreadsheet, but not all, like =$A$2:$Q$138;$A$140:$Q$147. Have you ever encountered such a problem? Is there a solution?
110293
I am trying to work out what script is required to have multiple timestamps on several tabs however each one I have tried doesn't work for me.  I am new to this. When text is added to column 2 I need a timestamp in column 1.  When text added in 8 I need a timestamp in 7.  When 15 which is a checkbox, a timestamp in 16, and when 18, timestamp in 17.  Is this possible to have so many? My current script is as follows and this is giving me the time stamp in column 1 when column 2 is amended and it doesnt overwrite when it is changed a second time. function onEdit(e) {   var row = e.range.getRow();   var col = e.range.getColumn();   if (col === 2 &amp;&amp; row &gt; 1 &amp;&amp; e.source.getActiveSheet().getName() === &quot;June 2021&quot;) {     if (e.source.getActiveSheet().getRange(row, 1).getValue() == &quot;&quot;) {       e.source.getActiveSheet().getRange(row, 1).setValue(new Date());     }   } }
65998
Some days I'm working with extremely large Excel files.  They have too many sheets that can't be shown in one sheet tab. (see image)      So, I'm wondering whether or not we can show Excel sheet on more than 1 row. Many tries with Google don't help much.    Many tries but I still can't make Excel show the sheet bar the way I want (≥ 2 rows), so I'm working with some VBA Scripts kopischke suggested.    FYI, right click on |&lt; &lt;&lt; &gt;&gt; &gt;| button on the bottom-right corner show you a list of up to 15 sheets.  For more, click on View more in the list appeared.
39171
I want to make an attendance sheet in excel. If a child is present, the teacher will write P in a cell, or A for absent, or L for latecomer. I want to get the number of P or L in a range to calculate total number of days in a month for each child, where the child has been either present or late.  How can I accomplish this?
81559
Unfortunately, as a new user I am not allowed to comment. This is why I need to do a new post regarding to this topic:   This is my code in Google Sheets Script:  function onEdit(e) {   if (e.range.getSheet().getSheetName() == 'Tabellenblatt1' &amp;&amp; e.range.getColumn() == 1) {     e.range.offset(0,1).setValue(new Date());   } }   It works perfectly when I enter data in cell 1 manually, but when data is added via API (in this case when data is added via Zapier) it does not work. Anybody can give me a hint why?
53323
I have 2 workbooks with 60 plus worksheets. I need to copy data from one workbook to the other but would like to capture and use the worksheet name within the cell. This is because when I create the workbook I make a master worksheet and then copy it 60 plus times. The above would make the cell ref for each worksheet the corresponding worksheet cell in the other file.
120799
I'm currently using the below script to timestamp when an edit is made to column 7 on a sheet named "Sheet 1".  function onEdit(e) {    if ([7].indexOf(e.range.columnStart) != -1) {    e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");    }   }     I have a second sheet "Sheet 2" which I need to run the same script on but against column 5.  function onEdit(e) {    if ([5].indexOf(e.range.columnStart) != -1) {    e.range.offset(0, 7).setValue(new Date()).setNumberFormat("dd.MM.yyyy at HH:mm");    }   }     I've tried .getSheetByName("Sheet1") but just cant seem to get it working correctly.
1307
I am completely new to coding. I am a teacher and trying to design a lesson planner tool using Google Sheets Scripts. The idea is to have a template sheet for the lesson that will copy into a new tab. That part is working fine. However, to delete the lesson I want to make it impossible for someone to accidentally delete the template. It is succeeding in not deleting the template tab if "Delete Lesson" is clicked.   However, it is displaying the alert that you cannot delete the template for the other tabs (but then deleting the tab as it should) This is the .  Any help is greatly appreciated!  I used this code at first:  function DeleteTab1() {     var spreadsheet = SpreadsheetApp.getActive();     var sheet = spreadsheet.getActiveSheet();     var sheetname = sheet.getName();     if(sheetname != "Template")  {      spreadsheet.deleteActiveSheet();    }    else {       SpreadsheetApp.getUi().alert('The template cannot be deleted');      }  }   Then I tried this and had the same problem:  function DeleteTab1() {    var spreadsheet = SpreadsheetApp.getActive();     var sheet = spreadsheet.getActiveSheet();     var sheetname = sheet.getName();     if(sheetname != "Template")  {      spreadsheet.deleteActiveSheet();    }    if(sheetname = "Template") {       SpreadsheetApp.getUi().alert('The template cannot be deleted');      }  }
87991
In my object record type i have created formula field. Now i want my formula field to work as below.  If Basement__c = NA and Property__r.Basement__c = NA OR  Basement__c = Crawl and Property__r.Basement__c = Crawl OR  Basement__c = Slab and Property__r.Basement__c = Slab Then it will return Type__c(formula field) = "MAtch"  And (Another rule) Basement__c DOES NOT contain any of the following "N/A,Crawl,Slab,None and Property__r.Basement__c Does not contain any of this then formula field will return "HAs basement"  How can i achieve this? Please help.
92591
I want to be sure all my Google Spreadsheet work can be converted OK to Excel. How many of the Excel formulas match the formulas of Google spreadsheets?
84487
I am using an external application to enter data into a Google Spreadsheet.  This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that manipulates and interprets the data.  My problem is that whenever I personally modify the referenced workbook, the referencing workbook updates just fine, but whenever the external application modifies it, it does not get updated.  I have tried editing the "Spreadsheet settings" so that recalculation is done every minute and on every change.  Furthermore, I have also installed an extension in Google Chrome that auto-refreshes the page every hour.  However, the data does not get re-imported from the referenced workbook.  Even if I copy the formula into a new cell, the data is still not re-imported.  Is there anything that I can do to the referencing workbook to trigger Google Sheets to re-import the data?  Edit: Just to be clear, I currently have one workbook with data entered by an external application in it (call it the "source sheet"), and another workbook with an IMPORTRANGE function in it (call it the "referencing sheet").  The data shown by the IMPORTRANGE function in the "referencing sheet" does not include any data entered by the external application since I last personally edited the "source sheet".  Also, both workbooks are using the new Google Sheets.  Edit: Also, this question is not the same as  because I am using the function given as the solution to that question to import data from a spreadsheet.  The problem is not how to import the data, but rather how to update the source for the data.  My assumption would be that Google would have taken care of this for me, but the data in the "referencing sheet" does not get updated, and the only way I can find to get it to update is to physically go into the "source sheet" and edit it myself.
60649
I have an Excel file that want to update and save automatically with out having to open it or manually interact with.  Manually, I open the file up and hit data refresh which goes and does a SQL query and then hit F9 for the formulas to update and then I just close/save.    (I then would mail the file out to people using a perl script or use SAS JMP to run some numbers/charts and also mail them out.  Basically I need to script some things which require the XLS file to be updated.)
35680
I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In my actual application the range is found by an array formula so I'm not sure if non array formulas will work here. My original function was {=LARGE(IF($A:$A=$A2,$B:$B),2)} dragged down the column. Replace 2 with 3 for the third largest, 4 for the fourth largest, etc. This worked except in the case of duplicates.  For example:  The data is in column 1 and the desired output is in column 2:  1    5  2    4 2    3 3    2  3    1 3 4 4 5   Things I have tried:   {=LARGE(IF(A:A &lt; LARGE(A:A,i-1),A:A),1)} for the i-th largest value.    -Inputting this gives 3 for both i = 3 and i = 4. =LARGE(A:A,COUNTIF(A:A,MAX(A:A))+1)   -This doesn't seem to be any different than just LARGE(A:A,2) {=MAX(IF(A:A &lt; LOOKUP(9.99999999999999E+307,A:A),A:A))}  -This does retrieve the value 4 but I'm not sure how to generalize it to the kth largest value.    VBA solutions are fine as well. I tried to code one myself but wasn't sure how to code in the array formula IF($A:$A=$A2,$B:$B).