Yes, it’s possible. One way is to use arrays and the QUERY function. For simplicity, let say that Columns A and B have the general information of the order Columns C and D have the data for version 1 Columns E and F have the data for version 2 Columns G and H have the … Read more
The IMPORTRANGE() function occupies a range based on the data that queries. If you add some content within the datarange that importrange returns, the latter will break because it can’t expand. You can either restrict the range that importrange occupies: =IMPORTRANGE(“SprdID”;”All Months!$A$1:$D14″) or add content starting from column E. You can also put the importrange … Read more
I think you had Lat, long wrong way around. Some mapping systems require Lat, Lng and others Lng, Lat (go figure!) If you change as below, you will get an image, but you may need to play with other parameters to get it working as you need. =IMAGE(“https://api.mapbox.com/styles/v1/mapbox/streets-v10/static/”&K3&”,”&J3&”,14.25,0,60/600×600?access_token=API_KEY”) You can also use the form below … Read more
Google Sheets: How to make a macro/script that, for all selected rows, inserts two rows beneath, then copies and pastes the content from the original? solved Google Sheets: How to make a macro/script that, for all selected rows, inserts two rows beneath, then copies and pastes the content from the original?
How to remove duplicate rows while keeping only first row based on duplicate data in B & C columns. Rows to be checked sequentially solved How to remove duplicate rows while keeping only first row based on duplicate data in B & C columns. Rows to be checked sequentially
google-spreadsheet solution: ‘for a CSV of all matches =arrayformula(TEXTJOIN(“, “, TRUE, IF(ISNUMBER(SEARCH(E2:E8, A2)), E2:E8, “”))) ‘for a CSV of the first three matches =replace(arrayformula(TEXTJOIN(“, “, TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, “”))), find(“|”, substitute(arrayformula(TEXTJOIN(“, “, TRUE, IF(isnumber(search(E3:E9, A3)), E3:E9, “”))), “,”, “|”, 3)&”|||”), len(A3), “”) excel-2016textjoin solution: ‘for a CSV of all matches input as array formula … Read more
???? I tried to figure out what your doing but I couldn’t get all of it. You have a lot of things wrong. Have you tried any examples in the documentation? That might be a good starting point for you. I think this is close to what you want but I’m not sure what you … Read more
I don’t think you need scripts. You could use conditional formatting. It seems the only reason you’re using VBA is because you need REGEX, which Microsoft excel doesn’t support except through VBA. Google Sheets however has REGEX support inbuilt. … highlights all Cells in Range C1 to E10000 which contain anything else than lowercase a–z, … Read more
The calendar id is declared on line 8 var calendarId = ‘bora-bora.dk_is0cr9ibe4thrs4mkqghvudrrk@group.calendar.google.com’; It is called by two functions: 1 = syncFromCalendar on line 223. Synchronize from calendar to spreadsheet. var calendar = CalendarApp.getCalendarById(calendarId); 2 = syncToCalendar on line 307. Synchronize from spreadsheet to calendar. var calendar = CalendarApp.getCalendarById(calendarId); The goal is to call the calendar … Read more
I believe your goal is as follows. You want to copy the cell “B” to “BW” when the cell “BY” is edited to 1. You want to run the script by a trigger. In this case, how about the following modification? Modified script: Please copy and paste the following script to the script editor and … Read more
=QUERY(Foglio1!A:C; “select B,SUM(C),MIN(A) group by B order by MIN(A)”) 2 solved QUERY and order by the first date of column
With Google Sheets use Query: =QUERY(A:A,”select A where A contains “”” & B3 &””””) Since you have the Excel tag use this formula for excel: =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:INDEX(A:A,MATCH(“ZZZ”,A:A)))/(ISNUMBER(SEARCH($B$3,$A$2:INDEX(A:A,MATCH(“ZZZ”,A:A))))),ROW(1:1))),””) Copy/drag it down sufficient for your needs. 5 solved Vlookup all the values matching the search term instead of just one.
There are two things you can do: If you deploy your script as a WebApp, you can execute it as “You” – that is the user from a GSuite domain who has an execution limit of 30 min. Design your script in a different way. Instead of waiting 10 minutes with Utilities.sleep(), incorporate a time-driven … Read more
If the original value is in seconds as in 5.323 for five seconds and 323 milliseconds, you can convert it to a dateserial value by dividing it like this: dataserial = seconds / 24 / 60 / 60 Append the resulting value to the spreadsheet and format the cell or the whole column as duration, … Read more
Tanaike’s code is a work of art, but I think it is based on an assumption that you would only run the script once. You’ve said that users will fill out a Google Form. You then manipulate this so that rows with identical columns will be transferred to one column. But ironically you then disassemble … Read more