google apps script – Duplicate and rename a sheet (only it`s data) at fixed hr and day

every monday I’ve to make a copy of a sheet which is called week_nn, where nn is a progressive number of the current week for that sheet from its beginning. At some point of the day, we have to fix those data to make a history, and so I duplicate the sheet which take the standard name of “copy_of_week_nn_WIP” to “copy_of_week_nn_FRZ”. If possible, I’d like to automathize this with a triggered script (time/day, in this case, every monday), because sometimes it happened if forgot the task and also, because it`s a stupid job…how I could possible do it?

Google Sheet filter issue – Web Applications Stack Exchange

I have a Google Sheet that collects responses from a Google Form, this collects contact details and preferred location. I shall call this workbook Feeder.

In a second Google Workbook (called placements) the data from the feeder sheet is imported into a sheet via =importrange. In addition, this workbook has a series of sheets for each preferred location. These sheets display the records for each preferred location using =filter (on the data that has been imported via =importrange).

Additional information is typed in associated columns for each record in the preferred location sheets.

My issue is if a new entry is made in the feeder worksheet – it will populate in the correct location sheet (which is sorted by last name) – but will misplace the “additional information” that has been typed for the existing records.

Any help would be really grateful received.

Array formula works in the sheet but invalid in conditional formatting

I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible.
I use start1 and end1 named ranges to refer to the interval endpoints and convenience is self-explanatory.

I couldn’t do it with ISBETWEEN, but the following formula appears to correctly return true for all such intervals and false for others:

=OR(ARRAYFORMULA(
  IF($C1<>"impossible", 
     IF(GTE($A1,FILTER(start1,convenience="impossible")),
        IF(LTE($A1,FILTER(finish1,convenience="impossible")),TRUE)
     )
  )))

However, when I enter this in Conditional Formatting it is rejected as invalid formula.

How can I add it to the conditional formatting rule?

Here’s the spreadsheet. The data in question is the sheet named “TEST” (the last one).
The formula has been entered in column P.

developing – Where can I get 4×5″, 5×7″, or 8×10″ color sheet film (or other hard-to-find exotic film) processed?

Background:

I’ve got a 4×5″ view camera, and I’d like to shoot some color negatives on it, for printing in a color darkroom that I currently have access to (that, alas, is slated to go away soon). However, the local lab doesn’t do C-41 sheet film, so… I’m wanting to figure out what lab(s) I might be able to send it to. And I figure this is becoming a more and more rare thing, so, I propose that this question be treated as a clearing-house for information about places that develop color sheet film, or color infrared film of any type (if there still are such places — I still have a roll or two in my freezer, but I don’t think it’s possible to get anymore, really), or what have you.

My suggestion is for someone to make this a community wiki (please; alas, I don’t seem to yet have the rep to do that), with a summary of all the answers in the question, broken down by film type. And then, the answers can be set up to be one answer per Lab that’s relevant to any of the listed film types, with comments under that answer for people to share their experiences with that particular lab.

I’m assuming that many places at this point will be oriented towards dealing with mail-in submissions from around the united states or even globally. Please specify the city, in case someone happens to be local to it, but also link to a website or provide whatever other contact info might be relevant, so that people can figure out mailing, etc.


4×5″ Sheet film

4×5″ Black and White:

  • LTI Photographic Services, New York, New York
  • Praus Productions, Inc., Rochester, New York
  • Peak Imaging, UK Sheffield, UK
  • Reed Art & Imaging, Denver, Colorado
  • North Coast Photographic Services, Carlsbad California —
    processes and scans just about anything 8×10 on down.

4×5″ E-6:

4×5″ C-41:

  • LTI Photographic Services, New York, New York
  • Praus Productions, Inc., Rochester, New York
  • Peak Imaging, UK Sheffield, UK
  • Keeble and Shuchat, Palo Alto — sends film to Swan Labs, in San Clamente
  • Reed Art & Imaging, Denver, Colorado

120 Roll film

I’m guessing this one is still a lot easier, but it’s probably harder than some, so… Break this down by Country/State, and list labs that people might find to be relatively local? (Do the same with other film types, as needed.)

California

  • Keeble and Shuchat, Palo Alto — processes B&W and C-41 in-store; will handle E-6, passing off to Swan Labs, in San Clamente
    Surely lots of others, especially in SoCal. Please add them.

New York State

Washington State

Anyone know of a place in Tacoma? Olympia? Others?

United Kingdom


B&W Infra-red

Surely many more


Color Infra-red

Anywhere?


  • Please add more film types below the above if you’re having trouble finding places that will process them. Be as specific as you think is relevant to the level of difficulty (I’m guessing that 35mm C-41 will still be a while before it needs to be listed here, since many drug stores and such still process it, for example — E-6 might be sooner, but even there, many drugstores will handle it for you, by passing it off to a lab on your behalf.)

  • Please make named links (see examples already existing), and if there’s an answer for that lab, link to that answer, instead of the lab’s website (linking to the latter from within the answer).

  • This is (or should soon be) a community wiki, so please edit as you see fit to make it better!

  • Please put a horizontal rule (three dashes) between major categories (types of film) only.

Access values from one Google Sheet in another

I need some values from Spreadsheet 2 (in Tab 2) in Spreadsheet 1 (in Tab 1).

Tab 1 and Tab 2

I don’t know the URL for each Spreadsheet; they are unknown.

How can I switch between these Tabs and get the values from Spreadsheet 2?

Is there a command for Google Apps Script?

backup – How to locate & compare script sources across a bunch of copies of the same G. Sheet ? It’s messy!

I’m getting used to coding in Sheets, using their script editor. I’ve coded a bunch of macros over time on a few projects, but one sheet in particular I keep multiple backups of (the data gets updated and I like to have old copies to compare).

Along the way, I sometimes get lost in versions and want to go back to find a version I did earlier. Ideally using some kind of compare tool or at least side by side in 2 windows (other than just search and view all copies one by one) ?

Questions:

  • Is there a smart & fast way to compare the code of various sheets (backups of the same sheet) ?
  • Or perhaps, a script search through all sheets scripts across my Drive ? I do keep local copies of my code, so can I search for a given variable, for example I put version numbers in my scripts, say v=0.3.9 so I’d find the sheet that has this script code version ?
  • Also: Should I avoid their editor, or use something else ? I’m an experienced coder, I do use WSL (Windows Subsystem for Linux), Visual Studio Code, Atom and Notepad++ to a varying degree (was previously a java coder for many years, so I’m re-learning these newer toolset, better suited for JavaScript).

Share only selected sheet with a scripted button/image to be executed by user

I have the MAIN big sheet and 2 other sheets (REVIEW and REVIEW_UI). MAIN is the central of my data where i don’t want other user to have access. The schema is like this:

MAIN
REVIEW
REVIEW_UI

I only want to give access of my ‘REVIEW_UI’ sheet. This sheet will serve as (UI) user-interface so user can input data. The UI looks like this :

enter image description here

In this REVIEW_UI, all other cells are protected ( user can view but can’t change) except there are 2 special cells i want to open to the user (B4 as SHOTID and E4 as REVIEW) , so they can write to it. Also there’s a SUBMIT button/image where a function script has been assigned to. So i want user to be able to click this image/button to run the script. Basically user will select SHOTID then write REVIEW then SUMBIT it.

The script will execute a task where it will send the data (that user has entered in the REVIEW_UI sheet) into REVIEW. So user need have access to REVIEW sheet as well (Not a direct access to view the sheet , but so the input from REVIEW_UI can get write access into the REVIEW ).

So summary :

  • MAIN : no public access, no share at all.
  • REVIEW : write access exclusively via button in REVIEW_UI.
  • REVIEW_UI : write access only to that 2 cells and can execute function by clicking SUBMIT button.

How can i manage those sharing setup and how to share with button/image still clickable, while i don’t want to share to user as editor ?

formulas – Google Sheet Filter and Match (List the label where a name exist.)

I am not 100% satisfied with the expandability of my solution, yet here it is:
if you have in B10 the name you want (ex: “Paul”) then paste the following formula:

=FILTER(A1:D1,{countif(A3:A5,B10),countif(B3:B5,B10),countif(C3:C5,B10),COUNTIF(D3:D5,B10)})

hope it helps

Joining two tables on a google sheet

Every day I will download a “product inventory” list from an third party source, the list is in the format like this:

Product Code Product Name Quantity
1 Apple 100
2 Pear 200
3 Banana 150
4 Grape 400

However, not all product shows up everyday. In the next day the report may look like this:

Product Code Product Name Quantity
1 Apple 120
3 Banana 200
4 Grape 320
5 Kiwi 80

And I want to be able to join these two tables and build an “product movement” report like the following

Product Code Product Name Delta
1 Apple +20
2 Pear -200
3 Banana +50
4 Grape -80
5 Kiwi +80

Could someone please give me some pointers on how to achieve this?

Google sheet formula explanation for stock market

=min(index(query(sort(GOOGLEFINANCE(“NSE:SBIN”,”low”,today()-100,100,”WEEKLY”),1,False),”select * limit 10″,1),,2))

This is a formula for finding low of last 10 weeks as far as i understand can some one explain me this formula in detail? It will help me…