Category: google-sheets

Auto Added by WPeMatico

apply a script on only a few sheets

function onEdit(e) {
var feuillelundi = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Lundi”);
var range = SpreadsheetApp.getActiveSheet().getActiveRange();
var cours, joueurs, coach, motifannul
var ligne = range.getRow();

for (var groupe=3;groupe<118;groupe = groupe +12) {
cours = feuille.getRange(ligne,groupe);
coach = feuille.getRange(ligne,groupe – 1);
motifannul = feuille.getRange(ligne,groupe + 1);

    for (var j=2;j<9;j++) {
        joueurs = feuille.getRange(ligne,groupe + j);
             if (!cours.getValue()) {
                  coach.setValue('REMPLIR');
             }
             if (cours.getValue()) {
                  motifannul.setValue('REMPLIR');
             }          
             if (!cours.getValue() && joueurs.getValue()) {
                     joueurs.setValue('FALSE');
             }
    }
}

}

Go to Source
Author: icekio

Copy from individual cell in Google Sheets Table Chart

I’m using appscript to build and insert a table chart into a sheet when a button has been pushed.

var chart = sheet.newChart()
.setChartType(Charts.ChartType.TABLE)
.addRange(outrange2)
.setPosition(2, 12, 35, -5)
.setOption('height', 445)
.setNumHeaders(1)
.setOption('page', 'enable')
.build();
sheet.insertChart(chart);

The chart gets created and inserted properly though my users are unable to interact with the contents of this table directly, they can only scroll and change pages.

I have checked to see if there were any required options that need to be enabled when before building the chart, the appscript guide references me to this page when advising on options:

https://developers.google.com/chart/interactive/docs/gallery/table

In the reference table and the overview above it states that “Users can select single rows either with the keyboard or the mouse. Users can sort rows by clicking on column headers. The header row remains fixed as the user scrolls. The table fires a number of events corresponding to user interaction”

The table that appears in my sheet is not like the one that appears in the reference and I am unable to copy text from the cells or select individual rows in the newly generated table. When attempting to interact with this table from google sheets it seemingly only allows us to resize and scroll up and down the table.

Are there ways to get around this or some attributes that I am missing to allow us to interact with individual entries from the table chart?

Go to Source
Author: Stephen Catchpole

#Error! in ImportJSON formula in google sheets

I have been successfully using the following ImportJSON formula in google sheets:

=ImportJSON(csgoempire.com/api/v2/metadata/roulette/history?seed=1997, “/”, “noInherit,noTruncate,noPrefixHeaders”,94)

But it was until recently that, the same formula started to give below #ERROR! result. Could you please help? It has been working perfectly for almost 2 month:

enter image description here

Go to Source
Author: Sabuhi

Android (mobile) Keyboard Shortcuts Google Sheets

In Google Sheets on a PC it is possible to use Ctrl+Shift+: to insert the current time into a (time formatted) cell in Google Sheets.

How can this be done when using a sheet on an android mobile phone keyboard?

IN edit:
I see that =now() will insert the current time (acknowledgements to @OlegS) but the cell has conditional formatting according to the time of day (in 3 bands – am, pm and evening). Entering the time using Ctrl+Shift+: applies the correct colour but =now() does not seem to respect the conditional format. The condition is:
(for example)

value is between 10:00:00 and 16:00:00

and the cell is given a different fill colour

Is this because the full result of =now() includes the date as well as the time in a complete string and is there a way within the function to limit the result just to the time element?

Go to Source
Author: User24601

How do I automatically match text to numbers and sum those numbers in Google Sheets/ Forms?

I am using a google form to collect responses from students. For each student I would like to match responses to specific numbers. Never = 0, Almost Never = 1, etc. Then sum the results for each student. So if a student answered five questions with “Almost Never” the end result would be 5. Responses are fixed allowing for a vlookup or match function.

Since it’s a google form, I’ve been using an ArrayFormula to sum each student’s responses. Something like this…

=ArrayFormula(G2:G+H2:H+I2:I+J2:J)

However this requires me to have a separate vlookup for each column to match the text to number. If I manually enter vlookup for each row, I can neatly combine vlookup and sum in one formula such as this…

=ArrayFormula(sum(vlookup(G2:S2,$A$10:$B$14,2,False)))

However this does not work for any future students. Is there a way to nest these formulas? Or another option? Ideally I would like to have one formula that for each row automatically matches fixed text responses to numbers, sums those numbers and can accommodate future rows/students responding.

Thanks for any help!

Go to Source
Author: jhaeff

In Google Sheets, analyze cell content, and if certain values are present, return a response in the next column

In the below example, I want to analyze each cell in column A, and if the cell’s contents match certain parameters, return a value in column B accordingly. So if the value in a col A cell = apple, banana or pineapple, it should return a value of “Fruit” in col B for that item. Or if the value of a cell in col A = truck or motorcycle, it should return “Vehicle” in col B.

Haven’t been able to figure this one out

ell

Go to Source
Author: Ben Dickey

FedEx Tracking in Google Sheets

I work for a company that sends packages out daily. We track the numbers in a Google sheet that populates via a Google form we use.

Recently we have had some packages go missing and I have been tasked with setting something up that allows us to watch the packages and make sure they are not getting lost in an effort to be proactive.

My thoughts were to do some sort of status updates with the tracking number, currently using lovelyAPI’s package tracker add on, but I would like something custom if possible.

enter image description here

Column x, y, z, aa, and ab are all populating through the package tracker and I have a formula to hyperlink the FedEx site and insert the value in F.

Is there a script that can do the same thing for me using the FedEx API?

I would also like to go a step further and have some sort of conditional formatting that would highlight the row in a certain color if the date I have in column A is older than 2 days and column Y does not return “delivered” or something like that. Can anyone help me out with this?

Trying to automate the whole thing but I am not but a mere peeon with little to no knowledge of coding.

Go to Source
Author: Noah Armstrong

How to conditionally format italics without changing other parameters?

I am using conditional formatting to color lines based on a priority value in column A, this works fine.

I would like to add another rule that would italicize the lines that match a specific parameter, without changing the background color the previous rules have set.
When I add this rule, the background color is overridden automatically. If I place the rule after the priority rules, I lose the italics.

Is there a way to change the italicization of a line only, with conditional formatting?

Go to Source
Author: JS Lavertu

Conditional Format IF Blank by 1st of Month

I am trying to create a google sheet to track payments for a team. I want a cell to turn red if a value hasn’t been entered by the 1st of the month. I have 2 other formats already set up to change to green or yellow depending on full payment or partial payment.

Thanks!

Go to Source
Author: Dan

Is there a way to get multiple share links from Google Drive in order?

I have 100s of files on my Google Drive where the files are sorted by name. All I want is the share link of each file in the order they appear in Google Drive.

I tried selecting all these files inside the folder and then click Share but it doesn’t respect the order of the files as they are displayed.

How can I do this?

Go to Source
Author: Joan Venge

Google Sheets: aggregate and extrapolate irregular data over time

  1. I have a list in which I now-and-then (no fixed interval) enter the available budget.

  2. Most of all I want to extrapolate this to a future trend of the coming 10 years. (no rocket science, more of an average through line of the last 4-5 samples, as you would have in stock market charts or so)

Possible as an inbetween step I would like to aggregate this to one average number per quarter. As in most quarters there is no data available, it should mean interpolation. (or interpolation for all: so regarding Jan/April/Juli/Oct 1st. it’s always interpolated between the neirest neighbours, fine with me…)

I am familiar with regular spreadsheet aggregate function, but I have no idea, what to use on such irregulary entered data. I have seen some stuff on extrapolition, but most was an sequences, (not a „sampled function“ if you will).

Sample Sheet

enter image description here

Thank you for your help 🙂

Go to Source
Author: Frank Nocke

Export Multiple Google Calendars to Google Sheets

I have several Google Calendars I’m subscribed to, one for each of my sales and logistics team members. I want to grab the events from each calendar for both today and tomorrow and put them into my main Logistics spreadsheet. I’m stuck on the “get all the events” part; I’m pretty sure I can get the right info to the cells I want them to once I’ve figured this out.

Here’s what I have so far, which is my newbie attempt to go through each calendar and grab the events. I’m getting the error that the parameters for CalendarApp.Calendar.getEventsForDay are incorrect.

Exception: The parameters (number) don’t match the method signature for CalendarApp.Calendar.getEventsForDay.
at calendarGrabTodayTomorrow(Code:85:28)

  var calendars = CalendarApp.getAllCalendars();
  var date = new Date();
  var dateTom = (date.getDate()+1);
    for (var i=0; i < calendars.length; i++) {
    var curCal = calendars[i];
    var eventsTod = curCal.getEventsForDay(date);
    var eventsTom = curCal.getEventsForDay(dateTom);
  Logger.log(eventsTod);
    Logger.log(eventsTom);}
}

Any help on this is greatly appreciated!

Go to Source
Author: Jeff Gibson

Google Sheets equivalent of Excel’s single accounting and double accounting underline styles without spacer columns

In Excel, if you go to Format > Cells… > Font > Underline, you can select from the Underline pulldown menu Single Accounting and Double Accounting underline styles, which, among other things, will leave a small space at the edges of each cell so that the underlines of adjacent cells are non-touching (see this accounting article for more on the concept).

In Google Sheets, I can replicate the look of this by using the cell’s bottom border (single or double) and including narrow spacer columns between cells that would otherwise be adjacent to replicate the spacing. However, that can be a headache and is certainly more effort than needed in Excel. Is there any better equivalent to Excel in Google Sheets?

Go to Source
Author: Vincent

How to filter out empty cells in Google Sheets With Query & Importrange

I am trying to use this formula but it’s not working. Do you hava an idea why?

QUERY(IMPORTRANGE(“IMPORTEDDATASHEET”;”06_20!M:AJ”);”SELECT SUM(Col17) WHERE Col1 = ‘Company Name’ AND Col23 <> ‘ ‘ label SUM(Col17) ””)
I want to filter by a specific company name in Col1 and filter out all empty cells in Col23 and then sum up col 17.

I also tried IS NOT NULL and != ‘ ‘ instead of <> ‘ ‘, it didn’t work either.

Go to Source
Author: N.K.

look for X at column A if X exists at A3 take data from C3 and give total to another Cell

enter image description here

I DONT MEAN ONLY C3,5,6,7 THATS JUST AN EXAMPLE I NEED IT TO LOOK AT THE WHOLE A COLUMN FOR A SPECİFİC WORD AND TAKE DATA FROM THE SAME ROW BUT DİFFERENT COLUMN İF X İS FOUND AT A3 THE DATA MUST BE TAKEN FROM C3 AND D3
İF X İS FOUND AT A357 İT MUST TAKE NUMBERS DATA FROM C357 AND D 357 … I DONT KNOW İF THİS İS POSSİBLE ? BUT İT SHOULD BE 😀 THANKS FOR READİNG BTW

Go to Source
Author: CANER COÅžKUN