Google Sheet script will not run on a tab linked to a Google Form

I wrote a script to move a row of data from one sheet (Sheet1) to another (Sheet2) when I type the word Yes in column one. I can get the script to work perfectly when the sheet is not collecting Form responses. When I have the first tab attached to a form my script will not run. Can someone please tell me what would prevent the script from running on a tab that is collecting responses?

office365 – Excel sheet to read data in real time from *shared* spreadsheet

I have an online quiz setup using Google Sheets, and I want to transfer it to Excel. Here’s what I have:

Team1,…,Team9 have their own ‘Answer Sheets’ which are Sheets with a cell, A2, where they input their answers. I have a ‘Master Sheet’ which simply has a column that imports the data from A2 from answer sheet 1, answer sheet 2, etc. The column is:

=IMPORTRANGE(“URL of Team1 answer sheet”,”Sheet1!A2″)

=IMPORTRANGE(“URL of Team9 answer sheet”,”Sheet1!A2″)

This means:

  1. Teams cannot see answers submitted by other teams.
  2. I can see everyone’s answers in real time. (Okay, not strictly real time, but only a delay of a few seconds at the most.)
  3. Teams don’t need to download additional software; they just open the Sheets in their browser.

All three are essential for the quiz to run smoothly.

Question: Is it possible to set this up using Excel? All teams have access to Office 365 but I cannot rely on teams being able to download the spreadsheets to use the Excel desktop app. Things I’ve tried / ideas I’ve had:

A: My Master spreadsheet contains links to other answer sheets, which I have then shared. The problem is that when someone else inputs their answer on their answer sheet, it doesn’t update on the Master sheet. I have to close and reopen the Master sheet, but even then this often produces errors associated with links being out of date.

B: (Haven’t tried this but I have doubts it’ll work) My Master spreadsheet is shared with everyone, and I have one worksheet per team, but I change the access so that Team1 only has access to worksheet 1, etc. Then, there is a worksheet only I have access to which reads the answers from all the worksheets. This will work, I think, but two issues: I want to scale this to 30+ teams and in my experience Excel shared sheets become very slow when so many collaborate on them at once. Also, it sounds like a nightmare to set up, changing permissions so every team has a unique point of view on the workbook.

Is it possible to accomplish what I’m trying to do?

I love Excel, but it’s sharing functionality in many respects seems to be far inferior to what Sheets is capable of at times.

Google Form to sheet to Doc, with calculations after pushing to sheet

NOTE! I am not a programmer I’m just fumbling through this! I’ve gotten as far as I can with searches, getting lost on multiple steps as most responses don’t integrate on thing to the next.

Background
Google Form pushes to Google Sheets and outputs a Google Doc prefilled template using GAS. This is for purchase order creation: Success It won’t let me post the code though…

Problem
I need the sheet to do some work once the form data populates (as an order PO, the form entry is quantity and price of each item and I need the total on the output PO). I need the resulting data to ALSO push to the Google Doc. This is where I’m getting stuck.

A.
Assign each line a unique ID using an array in an added column to the form response sheet
Successful in creating the ID, although it doesn’t push to the doc with the other data. Defining the variable with the others doesn’t work here.

Is this a var definition issue, or does this have to do with order of operations/triggers? Maybe the only data being sent is the FORM DATA, not the CREATED DATA IN SHEETS from the form.

The array formula that populates “poID” upon submitting the form data:

=arrayformula( if( len(A2:A), "" & text(row(A2:A) - row(A2) + 2, "1100")))

Google sheet form response tab

B.
On the third tab I created new cells to do calculations using the passed data from the form.

The data autofilled from the form doesn’t trigger the formulas on the second tab at all, as if the cells referenced were still empty (they are not).

Rows 2-6 are only populated in this image because I was making sure the formulas were correct, manually. When I took this screenshot there were already more form data lines on the response page that you can’t see on this tab.

Calculation Sheet

Obviously since these are not calculating on their own, there is nothing to pass to the form. Also cannot define those variables in the script.

Summary
I think this requires multiple scripts and/or multiple functions for this sheet.

Guessing since the array and calculations happen technically after the form has been submitted, it’s not passing. I can’t sort out how to:

  • define these variables (new script? new function?) and then,
  • perform calculations using that data, automatically (this must be an array thing like the poID is) and then,
  • get them to populate the same output document that the form data populates (new trigger?).

Any help here would be massively appreciated.

Appending current Share Price to Historic Daily Share Price on Google Sheet GOOGLEFINANCE Function Sparkline

I have a sparkline for the daily price for the last 30 days for a share in google sheets using the following formula (F1):

F1 =SPARKLINE(GOOGLEFINANCE(“GOOG”,”price”,Today()-30,Today()))

I can also get the current share price using a second formula (F2):

F2 =GOOGLEFINANCE(“GOOG”,”price”)

I was just wondering if there is a way to append this value (F2 output) onto the end of the sparkline returned from F1, to get a hybrid sparkline with the current SP shown.

Note: I could put the output of the table of historic data provided in F1 (just this bit: GOOGLEFINANCE(“GOOG”,”price”,Today()-30,Today()) into another sheet, and then append the current SP on the bottom of the output… but I wanted to be able to vary the number of historic days and the share in question, and have this for a large number of shares, so a formula solution would be preferred.

Thanks in advance.

Adding Button to add points to another sheet – Basketball Live Scorebook

I have created a scorebook sheet in google sheet for tracking live basketball stats.
One sheet with the scorebook table for the team with each player one a row.

I want to have a separate tab for data entry that populates the scorebook table.
This entry sheet will list the 5 players on the floor (column A will have jersey numbers which can change when players subs in)
I want to have a series of buttons to add 2pts/3pts/fouls/etc to the player listed in column A to the appropriate row on the scorebook table.

We have an app that does this beautifully, but it requires downloading a csv file to get the table data. I want to create a similar experience with a google sheet that fills in real-time.

My thought was to create buttons for each change. I have enough formula knowledge to know how to create a vlookup formula to look at column A and the row to find the player. But I need to make this a clickable button to change the data on the fly.

Any suggestions or script that I can add to the button would be helpful

I have a google form that feeds into a google sheet. I need to pipe that information into another tab/cell in a dynamic way based on a data validation

Lets see if I can explain this well enough…

I have a spreadsheet “Main_page” that is dynamically pulling information from a secondary sheet, “All_shots”. I also have 2 google forms that are activated via buttons on “Main_page”.

The google forms ask questions which pipe into two response pages.

What I’m trying to figure out is a way to do a vlookup that can interpret the data in a dynamic way.

The “Main Page” has dynamic information tied to cell C2. C2 is a data validation drop down that has a list of shot names. These shots names are pulled from column A2-A76 in “All_Shots”

So what I need to happen, and I cannot seem to wrap my head around how to do this….

I need a series of checks to happen and then a result to occur based on those checks.

On “Main_page” I need a script or a formula to look at “Main_page!C2” if that result is (for example) “FO_VFX_010” then I need it to look at my two form response pages to pull data

Form response page one: “Shot_version”. “Shot_version” needs to pull information from all of its cells in each row.

For instance. “Shot_version!B2” should be the thing to match to in “Main_page!C2” that’s the first part. once the script/formula has matched those two entries it needs to look at the next cell to the right which is “version_number” this will be a list of numbers in the format of v001, v002.v003 etc.

Somehow, and here’s where I’m stuck, I need to have either a “countif” or a “vlookup” to make the determination where that info is piped.

I want user input via a google form, to answer the following questions and then pipe the answers into “Main_page” but those answers will change to match whatever is in “Main_page!C2”

So the google form asks:

Shot Number (dropdown with list)
Version number (dropdown with list)
Artist’s notes (paragraph)
Date submitted (calendar)
Shot Status (drop down list)

On the “Main_page!H11” I want to dynamically take the contents of “Shot_version!B2 & “_” & C2″
So in this example “Main_page!H11” would read as “FO_VFX_010_v001”

Then M11 would have the date pulled from “Shot_version!E2”

The major hurdle I’m having is if all of that is piped in, and I change the dropdown in “Main_page!C2” going from “FO_VFX_010” to “FO_VFX_020” I need the contents of “Main_page!H11” to update to match the google form entry for that shot. so “Main_page!H11” would then read “FO_VFX_020_v001”

I have records under “Main_page!H11” that need to do the same thing. so “Main_page!H11” is v001
“Main_page!H13” is v002 (some merged cells in there) “Main_page!H15” is v003 etc.

To add insult to injury I have the same issue with my second google form.

“Main_page!O11” needs to reference “Client_notes!D2” but only if it first matches “Client_notes!B2” I need some way for the version number to be the thing that drives which cell gets which information.. and it has to dynamically switch whenever I change “Main_page!C2”

Everything is tied to “Main_page!C2”. It’s driving a bunch of other stuff using vlookup and a few other kitchen sink things. But I can’t seem to make that work for these cells. not sure what I’m doing wrong.

For instance I currently have:

=vlookup(C2,Shot_version!$B$2:$C$79,1,0) & "_" & vlookup(C2,Shot_version!$B$2:$C$79,2,0)

This does return the correct information as long as I’m on “Main_page!C2” with the dropdown set to “FO_VFX_010”

If I change it to “FO_VFX_020”, ALL of my “Main_page!H” cells change to the same answer of “FO_VFX_020_v001” I can’t figure out what I’m missing!

Oh and bonus points, I’d like the status in “Main_page!G5” to match whatever the most recent entry is on the “Shot_version!G2:G” column is. So if it’s ready for review on v001, but it’s rendering on v002, status would update to “rendering”

Here’s what Main_page looks like with the name set to “FO_VFX_010”

Here’s what Main_page looks like with the name set to “FO_VFX_020”

This is what the google form results look like for “Client_notes”

This is what the google form results look like for “Shot_version”

Google Sheet Freezes After Initial Calculation

I have an odd issue and can not figure out the problem. I have a google sheet that has been working great and everything is automatic on it. Nobody opens it. It receives data via Zapier when users fill out a Cognito Form. On a daily basis, it sends out a summary email of what was reported for that day. I noticed that the emails stopped working so I opened the sheet to investigate the issue. What happens is, when you open a Google sheet, you see a “Working” box at the top and then at the top right of your page you see the calculation progress bar fill up and disappear. This is really common on google sheets.

  • Well on this particular sheet, it literally becomes unresponsive after the initial calculation. I can click in a cell and navigate around cell to cell using my arrow keys when it opens but once that initial calculation stops, i cant move cells and it no longer responds to mouse clicks. I have to kill the page to open it again.
  • This has happened in the past. The fix was to duplicate the sheet and the copy worked just fine.
  • This time however, I duplicated it but even the copy is still stopping.
  • I DO NOT have any OnOpen scipts running. In fact, on the copy, i havent even enabled or scheduled any scripts to run and its still locking up.
  • As a test, during the initial few seconds i have access to the formulas, i reduced the data footprint and that didnt help.

Google Sheet “View only” – add my own comments (for my own viewing only)?

I have access to a Google Sheet that’s “View only” (so I can’t edit or comment).

I was wondering though if I could nonetheless add my own comments and other annotations for myself? (So for example, I’d like to be able to click on cell A1 and add a comment or change its color, but any such comments or color changes are visible only to me.)

(I know it’s possible to make a copy of the Sheet. However, the Sheet is periodically updated, so any copy I make would be quickly outdated.)

Or failing the above, is there a way to make a constant “live” copy of the Sheet? That is, my copy of the Sheet keeps updating according to the original sheet?

macos – SwiftUI Open a sheet from Buttons in a ForEach and tranfer a parameter

In a SwiftUI Mac App (Appkit) I want to open the IndexDialog sheet for each of the Buttons on click.
I want to transfer the corresponding index to the IndexDialog.
Regardless which Button is pressed, Index 1 is transfered.
How can this be done correctly?

struct TEST_Dialog: View {
  @State var showDialog = false
  
  var body: some View {
    VStack{
      ForEach(1...5, id: .self) { index in

        Button("(index)"){
          showDialog = true
        }
        .sheet(isPresented: $showDialog) {
          Print("index (index)")
          IndexDialog(isPresented: $showDialog, index:  index)
        }
      }
    }
  }
}

struct IndexDialog : View {
  @Binding var isPresented : Bool
  var index : Int

  var body: some View {

    VStack {
      Text("INDEX").font(.headline)
      Text(">(index)<").font(.callout)

      Button("Close") {
        isPresented = false
      }
    }.padding()
  }
}


Adjust data populated by query function in google sheet

https://docs.google.com/spreadsheets/d/1H6VCXzfcXKfsAEI7ZjU66NUWtE2TdPQUKTUKYS2gN3I/edit#gid=1414263054

I’m using query function in sheet(consultations) to bring all rows from sheet(cases list) that have either of these criteria

1- If column Q (disposition) is “consultation”

2- If Column Q (disposition) is “admission” AND column S is not empty.

But I want the column S from the master sheet (cases list) to be copied to column R in consultation sheet if criteria 2 is matched.

I used this formula but I couldn’t modify

=query(CASES,"select C,E,F,G,H,I,J,K,L,P,R,S where Q = 'Consultation' OR Q='Admission' AND S<>'' order by R ASC",0)

Note: I renamed data range of cases list sheet as “CASES”.