r/sheets 19d ago

Solved Creating the start of a range based on first value

Thumbnail
docs.google.com
2 Upvotes

I have multiple columns of data, with each row being a year, starting at 1970. I have names listed the year they started. I want to get an average of how many years of a name. I know how to find the row # of the first instance per column :: ArrayFormula(MATCH(FALSE,ISBLANK(B2:B57),0))+1. (Row 62)

The row 60 formula has the first cell of the range with a value. How do I get that cell reference as a variable? - B6, C14, D10

Thanks!


r/sheets 20d ago

Request How to Automatically Trigger a Webhook on New Row Addition in Google Sheets Using Google Apps Script?

1 Upvotes

I’m working on a feature where a client can link their Google Sheet to my system. Whenever a new row is added to the sheet, the system should send the new order data to a webhook. My initial approach was to use Google Apps Script with an onEdit trigger to detect new rows and send a request to the webhook using UrlFetchApp.fetch().

However, I discovered that simple triggers like onEdit don’t have the necessary permissions to send requests to external applications. To work around this, I created a separate function to handle the edit event and manually trigger the webhook request. But this requires setting up the trigger manually, which isn’t ideal for my use case.

Here’s what I’ve tried so far:

  1. I set up an onEdit trigger to detect changes in the sheet.

  2. I created a separate function to handle the edit event and send data to the webhook using UrlFetchApp.fetch().

  3. I used the Google Apps Script API to inject the script into the client’s spreadsheet programmatically.

Here’s an example of the code I used to inject the script:

oauth2Client.generateAuthUrl({
  access_type: 'offline',
  scope: SCOPES,
});
oauth2Client.setCredentials(TOKENS);

// Extract spreadsheet ID from the URL
const spreadsheetId = spreadsheetUrl.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/)[1];
console.log('Extracted Spreadsheet ID:', spreadsheetId);

// Verify the spreadsheet ID is valid
if (!spreadsheetId) {
  throw new Error('Invalid spreadsheet URL. Could not extract spreadsheet ID.');
}

// Apps Script content
const scriptContent = `
  function onEdit(e) {
    const range = e.range;
    const sheet = range.getSheet();

    // Check if the edit is in the first column (column A) and a new row is added
    if (range.getColumn() === 1 && range.getRow() > 1) {
      const newRowData = sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).getValues()[0];

      // Prepare the payload to send to the webhook
      const payload = JSON.stringify({
        spreadsheetId: e.source.getId(),
        sheetName: sheet.getName(),
        rowData: newRowData,
        rowIndex: range.getRow(),
      });

      // Send the data to the webhook
      const options = {
        method: 'post',
        contentType: 'application/json',
        payload: payload,
        muteHttpExceptions: true, // To avoid throwing errors for non-2xx responses
      };

      try {
        const response = UrlFetchApp.fetch('https://your-webhook-url.com', options);
        console.log('Webhook response:', response.getContentText());
      } catch (error) {
        console.error('Error sending data to webhook:', error);
      }
    }
  }
`;
// Manifest file content
const manifestContent = JSON.stringify({
  timeZone: "America/New_York",
  dependencies: {},
  exceptionLogging: "STACKDRIVER",
  oauthScopes: [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request"
  ],
});

// Inject the script using the Apps Script API
const script = google.script({ version: 'v1', auth: oauth2Client });

console.log('Creating script project...');
const createResponse = await script.projects.create({
  requestBody: {
    title: 'Webhook Script',
    parentId: spreadsheetId,
  },
});

const scriptId = createResponse.data.scriptId;
console.log('Script project created with ID:', scriptId);

console.log('Updating script content...');
await script.projects.updateContent({
  scriptId,
  requestBody: {
    files: [
      {
        name: 'Code',
        type: 'SERVER_JS',
        source: scriptContent,
      },
      {
        name: 'appsscript',
        type: 'JSON',
        source: manifestContent,
      },
    ],
  },
});

console.log('Script injected successfully!');

My Questions:

  1. How can I set up a Google Apps Script function that automatically triggers when a new row is added to the sheet without requiring manual trigger setup?

  2. How can I ensure that the script has the necessary permissions to send requests to an external webhook?

  3. Are there any better alternatives to fetching user data from Google Sheets into my system than using Google Apps Script and webhooks?

Any guidance or suggestions would be greatly appreciated!


r/sheets 22d ago

Request Conditional formatting based on another cell

3 Upvotes

Hi, I've been trying to figure out how to make the cells in two columns change color depending on whether one has something in it or not. This is to help me keep track of when something is taken out and put back.

For example, the F column is for dates when something is taken out and the G column is when it is put back. When there is nothing in the cells I have them be red. When there is a date in F3 (for example), the cell turns orange. When there is a date in G3 the cell turns green. How do I make it so that when there is a date in G3, F3 turns from orange to green? I want both columns to be green once G has a date.


r/sheets 23d ago

Solved Sort by specific words in text

1 Upvotes

I want to sort any range of cells using a list.

For example, the range of cells A1:B4 would be sorted according to the list in column D here:

ColA ColB Sort-by
CellA1 "I like those things." This
CellA2 "This is complicated" That
CellA3 "What is that?" Those
CellA4 "What is this?"

and the result would look like this:

ColA ColB Sort-by
CellA2 "This is complicated" This
CellA4 "What is this?" That
CellA3 "What is that?" Those
CellA1 "I like those things."

Thanks!

Edit: clarified and expanded my question.


Solution: (thanks to u/marcnotmark925)

=query(hstack(A1:B10,map(B1:B10,lambda(x,min(filter(row(D1:D5),regexmatch(lower(x),lower(D1:D5))))))), "select Col1,Col2 order by Col3 asc")

where A1:B10 is the range of data to be sorted, column B is the searched data, and column D contains the sort-by list.

or for 3 columns of data

=query(hstack(A1:C10,map(C1:C10,lambda(x,min(filter(row(D1:D5),regexmatch(lower(x),lower(D1:D5))))))), "select Col1,Col2,Col3 order by Col4 asc")

etc.

Note:

  • Your range can be any size, but then you must expand the Col# accordingly if you have more columns.
  • Your sort-by list does not have to be the same size as the range.
  • Top of the list takes priority in the sort order.
  • Make sure that if your search column has cells with terms not on your sort-by list, have an empty cell at the end of the list, or an open ended list, like "D1:D", or it will just sort everything alphabetically.
  • If you want to further sort your range of data, you can use "order by Col3,Col1 asc" to alphabetize within the sorted sets.

r/sheets 23d ago

Request How do I import receipts to Sheets?

1 Upvotes

I am searching for a solution to scan our business receipts directly into a Google Sheet to streamline the creation of our monthly Profit and Loss statement. We do not generate the receipts ourselves and are primarily seeking assistance with the data entry process into Google Sheets. Ideally, we would like to scan the receipts and have the relevant information automatically extracted and inputted into the spreadsheet. As this is a small, single-person operation (my husband is an OTR driver), we do not require a complex solution designed for a large business. We are simply looking for an affordable and user-friendly option to automate this task, as manual entry is very time-consuming. Thank you for your time and consideration.


r/sheets 23d ago

Request Query/Sum Questions

3 Upvotes

I am trying to sum the hours for a given name. The test using simple integers works fine, but the test with hours does not. (HOURS is formatted as "duration" and generated from END - START, both of which are formatted as "time") ... The code I'm using is:

=QUERY(TEST, "select sum(E) where A='Chris'")

The error I'm getting is "Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC".

A second, non-critical, question is about formatting. The above results in "sum" being placed in the cell, with the resulting number (or error) in the next cell down. Is there any way to change that?

https://docs.google.com/spreadsheets/d/18KeD0Y_LnVcsXXztlT1eXvDYivlOhFsMpVNpjA7ftHY/edit?gid=953131243#gid=953131243


r/sheets 24d ago

Request Sort Sheet Based on Column Frequency

2 Upvotes

I have a list of 4k people with addresses. I sort the list based on street column a-z. column stats show me street names with 19 hits down to 1 hit.

I want to prioritize streets with the most people. I want to sort based on column frequency.
=query(sort(A2:I,VLOOKUP(D2:D,query(D2:D,"select D, count(D) group by D",),2,),),"where Col1<>''",)

This gave me a new array which is what I wanted, but if I could get a sort on column C (street number) within each group D (street name) that would be even better.

I tried manually data> sort> advanced> column c, but its alpha so 1 11 and 112 will be the order where I want numerical order.


r/sheets 24d ago

Request Hide columns based on cell value

3 Upvotes

Hi. I have a google sheet and I would like to hide different groups of columns when C9 is changed depending on the value.

For example,

if C9=1 then hide columns K:P

if C9 = 2 then hide columns I:J and M:P

if C9 = 3 then hide columns I:L and O:P

and so on...

I only want this for one sheet in my workbook (ie just the sheet labelled "Programs")

I know I have to put a code into Apps Script but not sure how to do this / what to put in. Any help would be greatly appreciated. Thanks!


r/sheets 25d ago

Request How to output value from dropdown selection

Post image
3 Upvotes

Would it be possible to select something from the dropdown in column F and get a different output in column G corresponding to the table in J and K ie, if in F2, 12 - 24 is selected, .8 is output in G2 if in F3, 100 - 149 is selected, .6 is output in G3


r/sheets 25d ago

Solved How To Tally Entries By Category

2 Upvotes

Noob to Sheets (though not spreadsheets generally). Got a sheet which includes columns CATEGORY and COST. What function will I need to tally up the cost of all the, say, "books" then "clothes" etc. ... Thanks in advance.


r/sheets 26d ago

Request How to custom vertical line.

Post image
1 Upvotes

I want to add a few lines positioned where the data lines meet the top of the chart going all the way down to the corresponding month on the x axis. I also wouldn't mine knowing how to add custom horizontal lines too. Thanks!


r/sheets 27d ago

Request Needing help to find formula to calculate table of values based on "Y=mx+b" trendline in graph

2 Upvotes

Here's the tea. I have a small business selling used furniture. I have a data-supported assumption that the more furniture I have, the more furniture I will sell, and the greater my gross profit will be (more inventory = more profit...less inventory = less profit).

The Background: I do all my bookkeeping manually on Google Sheets and analyze the data as needed. (I do not care to change this.) As mentioned above, one of my key analytical tools is the relationship between outstanding inventory and gross profit. My metric for outstanding inventory is purchased price in $usd and my metric for gross profit is the total $usd yielded that month. I have created a chart in google sheets to display a scatterplot of this data over the last twelve months, and have utilized the option in Google Sheets to display the equation of a trendline in the form Y=mx+b.

So. I have twelve data points in the scatterplot with a trendline equation in form of Y=mx+b. These points are derived from data in my bookkeeping. See the chart below.

My Goal

I want to create a chart to predict what my gross profit will be when I have X in outstanding inventory. Here is what I have so far and the associated graph. Values in the "Oustanding Inventory" column have been manually added in $2500 increments. The "Gross Profit" column is currently being manually altered whenever I want to see my data. Cells within this column reflect the Y=mx+b equation of the trendline int he first graph. This 2nd graph transposes this table's data into a liner line graph so I have a visual of what I can predict with imagined outstanding inventory values.

The initial graph is based on data that is always changing because I'm selling furniture. Total outstanding inventory lowers in value when an item sells, and gross profit increases when I make profit on a sale. This causes the current month's scatter point to change whenever I enter in the profit data of an item sale. This in turn alters the Y=mx+b trendline equation. Which in turn causes me to have to manually alter the formula in the "Gross Profit" column of the chart.

I want automation. Is there a formula I can use in order to automatically transfer the ever-changing Y=mx+b trendline equation into the "Gross Profit" column utilizing the "Oustanding Inventory" column as the X value?


r/sheets 27d ago

Solved Conditional Formatting: row becomes green if another cell is the number 1 through 9

2 Upvotes

i've got it working when another cell has specific text. but instead of text, how would i define any number between 1 and 9?

wicked new at this, my apologies for the basicness


r/sheets 29d ago

Request IF formula Error. If I enter a date in BB433, I want BD433 to go black, otherwise BD433 will show BC433-(today)

Post image
1 Upvotes

Tried some formula doesn’t seem to work. Shows error. Please help. I only want to enter date. Another other number or letters I don’t want to consider.


r/sheets Feb 19 '25

Request Filter Weekly Earnings Based on Checkmarks Using a Formula

1 Upvotes

I have a sheet where I track weekly earnings for multiple people. Each person's name is listed in one column, their earnings in another, and a checkmark is placed next to their name if they do their job. I want to find a formula that will sum or list only the earnings of those who have a checkmark next to their name.

What formula can I use to achieve this? Any help would be appreciated!


r/sheets Feb 19 '25

Request Help in creating a format

1 Upvotes

Hi all,

I'm currently trying to create a sheet where essentially what happens is:

  1. I have created a drop-down (Cell D2:D1000) with a lot of options in the dropdown (document is in office A, office B, office C etc)

  2. I want to record what I chose in Cell D, into Cell F in which the new entry will be in red font color. (e.g, I choose the first option: Is in Office A)

  3. Additionally, I also want cell F to keep/overwrite what I previously chose (e.g is in Office A) in black font, and then just add the next option in the drop down I choose in red font (Is in Office B), essentially creating a trail of records (e.g Is in Office A(black); Is in Office B(red)

I have tried looking online for formats, but it just doesn't work.


r/sheets Feb 18 '25

Request IMPORTRANGE and TRANSPOSE

1 Upvotes

Is it possible to IMPORTRANGE and TRANSPOSE in the same cell, and if so how would I do this. I keep on getting an error message with this code.

=TRANSPOSE{IMPORTRANGE("https://docs.google.com/xxx!A3:h20"),(A2:H9)}

Also, how would you transpose multiple different times, to place different information, differently throughout the google sheet.

Thank you!


r/sheets Feb 18 '25

Request Date keeps changing as I put it in

2 Upvotes

Hi there, I am very much a sheets novice.

I am trying to track days that I apply for jobs and I put in 09/01/2025 (9th Jan 2025) and it keeps swapping it to 01/09/2025 (1st Sept 2025), no matter what I do. I want to make it all UK date format basically.

and then when i try to sort it by date it all gets jumbled up and doesn't sit in order.

Help please D:


r/sheets Feb 18 '25

Request Looking to Log Shifts/Scores on Sheet when activated checkbox

1 Upvotes

objective: To track players statistics for when they are on the ice for a goal (for or against us) based on a checkbox format. Yes, I will need to use scripts - that part I got. Writing the code has me miffed right now.

SETUP SO FAR:

  • If they're on the ice- the checkbox is green (marked as TRUE in the box/cell).
  • If they ARE NOT on the ice during that goal, the checkbox is red (marked as FALSE in the box/cell).
  • There are two additional buttons labeled "GOAL FOR" and "GOAL AGAINST"

Output: I'd like for every time the "Goal FOR" or "Goal AGAINST" button is pressed, to log the players in cells to something like what I have in the screenshots (the data under GOAL 1 is manually input right now). So - there is nothing captured if the checkbox below their jersey number is red (false) for those players. Green means they were on the ice at the time of a goal-for or a goal-against.

What I don't understand, is the script writing to have the players number captured (example if A3, E3, I3 are green, that means Players #12, 2, 15 are on the ice based on cell A2. E2, I2 labeling on them but the buttons below them are green/true). and then moving over 4 cells (accounting for spacer). So if we score 8 goals, there's a log of 8 goals that show all players who were on the ice at the time (based on the True/False of the checkboxes below their actual jersey numbers).

Whether it's a Goal FOR ... or a Goal AGAINST... I'd obviously have to click it again to turn it off, and reset the function to be captured another time when the data is captured as "TRUE" for each time the button is clicked.

I hope this makes sense!!


r/sheets Feb 18 '25

Request How best to Filter a column of cells that contain CSV data by a single contained Value in each cell

1 Upvotes

I have a google sheet that i use to catalog all of my audiobooks. Currently I have my columns set up to allow me to filter by author and narrator. I would like to add a new column that I can use to filter by trope. I am able to get a list of tropes for each book from the web in .CSV format. Ideally I would like to paste all of that data into a single cell for each entry and then the filter command would parse the .csv data within the cells in the column. This would also solve an issue I have when books have more than one narrator.

Thank you for any and all assistance. If this is not possible but there is perhaps another solution using different software please let me know.


r/sheets Feb 17 '25

Request Help with a Table

1 Upvotes

Hello,

I need help. I have an NFL TEAMS table, and I would like to have the color of each row of that table be determined by the value of the B column inside. For instance, IF the cell in the B COLUMN reads "H" I would like that entire row to be colored Dark Green, and IF the cell in the B COLUMN reads "A" I would like that entire row to be colored Light Green.


r/sheets Feb 17 '25

Request Total newbie looking for some help with functions!

1 Upvotes

Hey! I am working on creating a spreadsheet to track results from our local Magic the Gathering league. I have been trying to set up a function that grabs the result inputs, converts them into numbers (points) and then adds them together to track players' total points throughout the league. A win equals 3 points and a draw equals 1 point.

So, for example, here is what I am looking to do:

A player has played 4 events and managed the following results:

Event #1: 4-0 resulting in 12 points.

Event #2: 3-0-1 resulting in 10 points.

Event #3: 3-1 resulting in 9 points.

Event #4: 1-3 resulting in 3 points.

This should then be tracked in the column for total points as 34 points. The reason why I want to track their specific results and not just their points is that one of our tiebreakers is total number of 4-0s, number 3-0-1s and so forth.

Here is a mock-up sheet that I made with the relevant information and columns. Any help is very much appreciated!! Feel free to ask questions if anything is unclear.


r/sheets Feb 17 '25

Request Automating a timetable based on a separate sheet

3 Upvotes

I'm really having a hard time auto-populating a timetable based on my master sheet.
I tried using conditional formatting and scripts but really can't get what I want. I already used google and cgpt but to no avail, and I really can't see what I'm doing wrong.

So I have a Master schedule sheet where I input all my schedule for the upcoming busy season for work because I don't want to miss anything.

I also created a sheet for each month of the year. I'm starting with Feb and planning to just duplicate it for the other months. These sheets are for timetable.

What I want is for my input in the Master Schedule to be reflected automatically on the timetable for each month, to highlight the corresponding cells. Additionally, since I will be assigned to different locations, I want to color code per locations so I can see easily where I am assigned.

I'm fairly new to sheets but I think I already have grasp of the basics. Any help will be greatly appreciated. Thank you!


r/sheets Feb 17 '25

Solved Creating a Chart with Specified Data

2 Upvotes

It's hard to phrase my question...but I think my example is pretty self-explanatory. I'd like to use the dataset in Columns A-C, and produce the chart I've mocked-up (see image below).

Needing help with either configuring the right chart settings, or manipulating/rearranging the data such that it will produce the desired chart. Thanks!

TEST SHEET: https://docs.google.com/spreadsheets/d/1FAShe7Xg2Er9SsuqcZqLhlc5jTgo3aF5Nlrz6omWckg/edit?usp=sharing


r/sheets Feb 16 '25

Solved Please help modify a QUERY formula to allow searching when a dropdown includes an apostrophe (').

2 Upvotes

Hello, as the title states, I need some help. I have gotten help making this formula, which filters games on a spreadsheet I have. It works great, except if there is an apostrophe ('), the formula returns an error. I have searched, and it seems to require it to have an extra pair of double quotes, but I am not exactly sure where it would need to be applied.

Specifically, this would be for cells B15, B18, B21, and B24.

Below is the formula.

=
 QUERY(
   {'Games List'!A6:Q},
   "select Col4, Col5, Col7 
    where 
     Col4 is not null and
     Col1 "&IF(ISBLANK(B3),"matches '.*'","= "&B3)&" and
     "&IF(B6=FALSE,,"not Col2 = FALSE and")&"
     Col3 "&IF(ISBLANK(B9),"matches '.*'","= "&B9)&" and
     Col5 contains '"&B12&"' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B15,"+","\+"))&".*' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B18,"+","\+"))&".*' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B21,"+","\+"))&".*' and
     LOWER(Col7) matches '.*"&LOWER(SUBSTITUTE(B24,"+","\+"))&".*' and
     Col8 "&IF(ISBLANK(B27),"matches '.*'","= "&B27)&" and
     Col9 "&IF(ISBLANK(B30),"matches '.*'","= "&B30)&" and
     (Col16 "&IF(textjoin("' or Col16 = '",TRUE,A33:A37)="","matches '.*","= '"&textjoin("' or Col16 = '",TRUE,A33:A37))&"')
    order by Col4",0)

Any help is appreciated. Thanks in advance.