google sheets – Reducing amount of IMPORTHTML Requests

Main Question

I’m trying to reduce my IMPORTHTML requests. The main issue is I’m forced to do the process twice.

=INDEX(IMPORTHTML(*Link*,"Table", 1),ROWS(IMPORTHTML(*Link*,"Table", 1)),2)

Is there any way to index the last row of the table without having to reinitialize the table again? The table size is random between 4-12. This wouldn’t be an issue if it wasn’t for the fact I’m using this 5 times (different cells from the imported table) across 282 tables. 1410 total cells like this. Over 2820 IMPORTHTML requests…

If I could remove the second request within the formula, finding the last row some other way, or re-reference the initial request, I could cut the requests in half. 1410 is still a lot, but it’s faster than 2820.

Ideally, I’d like to reference the information of a full table within a single cell. Then I could reference the cell whenever I needed the table. Then I’d be down to 282 requests.

If I absolutely have to, I can remove 2 of the columns as the main usage of this chart only needs 3 (Current Team, Career Hits/Strikeout, Career Homeruns/Shutouts). This would reduce it to 1692 requests.


Specific Information

A Simplified version of the sheet I’m working on.

Here’s what I’m trying to do on each page. I’m sure there are ways to combine some of these into a single step, but I didn’t want my formula’s to get so long leaving me unable to find errors. The original sheet has 20 teams.

  • TeamScraper: Pull the current players on each team and create 2 lists
    (Batters and Pitchers)
  • TeamTranspose: Rotate the list to be vertical.
  • PlayerStack: Take all the players and organize them into 2 lists(Batters and Pitchers). Create 2 more lists with the names reformatted for the LinkBuilder.
  • LinkBuilder: Turn each of the names into a link of the webpage with that player’s stats table.
  • Batters & Pitchers: Pull needed stats from the table relevant to that player.

Don’t mind Baby Triumphant. He’s one of the few cases where the name he’s given in the team list isn’t the one used by the stats website. I’m working on an Exceptions page that the reformatting process will reference.

Edit: While writing this I saw someone asking about too many IMPORTHTMLs. The answer-er mentioned a limit of 50 instances of the request per sheet. I find this odd considering my 2820 requests split between 2 sheets EVENTUALLY works. It’s just slow. Maybe they mean only 50 requests every few minutes?

google sheets – Removing Last Updated value when No data was selected from the column to check

I need help on my Google Sheet script. It is working fine on showing the timestamp of Last Updated at colB every time a new Call Status was set at ColA ; but when I deleted the status at colA, the timestamp remains at colB. It should also be clear once no data was selected at colA.

Here is my google sheet>> t.ly/z2P0

Here is my script>>

var timezone = "UTC+00:00";
var timestamp_format = "hh:mm:ss dd/MM/yyyy"; // Timestamp Format.
var updateColName = "Call Status";
var timeStampColName = "Last Updated";

var sheet = event.source.getSheetByName('StatusDate'); //Name of the sheet where you want to run this script.
var actRng = event.source.getActiveRange();
var editColumn = actRng.getColumn();
var index = actRng.getRowIndex();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
var dateCol = headers(0).indexOf(timeStampColName);
var updateCol = headers(0).indexOf(updateColName); updateCol = updateCol+1;
if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
var cell = sheet.getRange(index, dateCol + 1);
var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
cell.setValue(date);

Thank you so much for the help.

google sheets – concatenation truncating numberic value

Answer updated

(as a reply to your edit, comment as well as your own accepted answer.)

I am afraid you jump to the wrong conclusions thus also misleading the readers.

Why? Because…

Just as in this hidden/missing text.
1. In your statement “in a cell J21 I have a set value 1.10000” you merely reveal what is present in the cell.
2. Further more you fail to show how this value was placed there (whether manually or as a result of a formula/script).
3. Not even how it is formatted or presented.
4. More so whether the value is a number or text.
5. Needless to mention the reference to its Horizontal alignment (in your later added image)
6. Finally, you do not take into consideration any of the aforementioned points even after your edited question or more so in your accepted answer.

The above may seem to be insignificant, still truly essential and should not be missed for the right set of conclusions.

Thus you arrive to a wrong/partial assumption

“concatenate doesn’t work in …as both solutions still truncate the numeric result.”

All given solutions (yours and mine) work.

The correct conclusion

The issue does NOT lay on CONCATENATE itself.
Depending on the situation different solutions may apply.

When asking a question please share all relevant info and even better share an editable sheet so you can be easier helped.


Original answer


You should change your formula syntax to

=CONCATENATE("@ £/€ rate: ",(J2))

You could also use

="@ £/€ rate: "&J2

(Please adjust ranges to your needs)

enter image description here

Functions used:

Google Forms/ Sheets – Web Applications Stack Exchange

Taking into consideration the little info you give, I imagine that if your response sheet (Form Responses) looks something like this:

+---------------------+-----------+------------+---------------------------+-------+
|      Timestamp      | Last name | First name |           Email           | Steps |
+---------------------+-----------+------------+---------------------------+-------+
| 2020-10-19 23:25:51 | Boc       | Florencia  | fboc9@vinaora.com         |   356 |
| 2020-10-19 22:33:29 | Boc       | Florencia  | fboc9@vinaora.com         |   175 |
| 2020-10-19 19:58:08 | Splevins  | Donny      | dsplevins7@ucla.edu       |   153 |
| 2020-10-19 18:40:52 | Castagne  | Cedric     | ccastagneb@ow.ly          |   382 |
| 2020-10-19 17:56:40 | Gipp      | Eloisa     | egipp4@webmd.com          |   147 |
| 2020-10-19 17:26:39 | Splevins  | Donny      | dsplevins7@ucla.edu       |   365 |
| 2020-10-19 17:09:17 | Riccardi  | Monica     | mriccardi6@wikispaces.com |   289 |
| 2020-10-19 17:07:08 | Boc       | Florencia  | fboc9@vinaora.com         |   284 |
| 2020-10-19 16:54:45 | Gipp      | Eloisa     | egipp4@webmd.com          |   174 |
| 2020-10-19 14:20:50 | Riccardi  | Monica     | mriccardi6@wikispaces.com |   233 |
| 2020-10-19 14:02:00 | Splevins  | Donny      | dsplevins7@ucla.edu       |   173 |
| 2020-10-19 09:51:38 | Riccardi  | Monica     | mriccardi6@wikispaces.com |   177 |
| 2020-10-19 04:49:03 | Splevins  | Donny      | dsplevins7@ucla.edu       |   320 |
+---------------------+-----------+------------+---------------------------+-------+

If so, you then need to create a new sheet and place the following QUERY formula i cell A1

=QUERY('Form Responses'!A1:E, 
    "select B, C, sum(E) where A is not null group by B, C ",1)

(Please adjust ranges to your needs)

enter image description here

If still in need please let us know.

import CSV attachment from gmail to google sheets

I receive a daily email with an attachment. I have the options of a CSV attachment or xlsx attachment. I currently and working to import the attachment into google sheet from my Gmail. I have attempted the script below and only receive the result “PK” in the first cell of the google sheet. The result should be a spreadsheet with many columns and rows. I am new at this and need help to make this code work. My plan is to set a trigger that will run this script every day and replace the old data. Can anyone recommend a code that will work with CSV or XLSX?

google sheets – A Script that Allows a Checkbox to Recognise a Value via Formula and Copy to Another Cell

Is it possible for:

function onEdit(e) {
const sheet = e.range.getSheet();
if (sheet.getName() !== 'Sheet1' || e.range.columnStart < 3 || e.range.columnEnd > 3 || !String(e.value).match(/^(true)$/i)) {
return;
}
const valueToCopy = sheet.getRange(e.range.rowStart, 2).getValue();
const targetRange = sheet.getRange(e.range.rowStart, e.range.columnStart + 5);
targetRange.setValue(valueToCopy);
}

Please see the (Sample Sheet)(1) attached for a more detailed explanation.

Look forward to hearing from you.
Thanks,
DDS

– P.S not sure how to enter links that well on here :’D so:
https://docs.google.com/spreadsheets/d/12VY4RLaoReFHw2SSa4LWNjtp4rwr9GD-NLKdmvdqe9U/edit#gid=0

How to use “aggregate” chart feature on Google Sheets

With some playing around, I figured out what the aggregate feature does.

Basically, the aggregate feature allows you to aggregate all values that share an identical x-axis key.

Normally, if you have duplicate x-axis keys, your chart will look something like this, with values having the same key forming vertical lines:

enter image description here

(I’ve increased the width of the lines to make it easier to see what’s happening.)

But perhaps this isn’t the behavior you want. Perhaps, instead, you want all y-values with the same x-value to be summed, and only the summation to be plotted. The aggregate feature lets you do this.

First, turn on the aggregate feature, and select the desired aggregation method:

enter image description here

After making these edits, instead of y-values with identical x-values resulting in vertical bars, the aggregation of the y-values for a given x-values will be graphed:

enter image description here

google sheets – Trying to pull Duplicate data from multiple columns

To avoid the error message you could wrap your formula with the IFERROR function:

=IFERROR({QUERY(A1:B3,"SELECT * WHERE A contains """&E2&""""); 
         QUERY(A1:B3,"SELECT * WHERE B contains """&E2&"""")}, 
         "No match")

enter image description here

EDIT

Ok, but there is supposed to match, I’m sorry I’m confused. I’m not looking to just eliminate the error message. Since “John” appears in ColumnA, why is it No Match?

Though the OP’s closing question is “How can I rectify this?”, I will follow the comment and explain.

What you want to do using the curly brackets {}, is to have your results displayed one under the other, like ={1;2}

+---+---+------------+--------+--------+
| A | B |  formula   | result | result |
+---+---+------------+--------+--------+
| 1 | 2 | ={1,2;3,4} |        |        |
| 3 | 4 |            |      1 |      2 |
|   |   |            |      3 |      4 |
+---+---+------------+--------+--------+

Only in this case, as an example for John, because your second query completes with an empty output it is as if you are asking for:

+---+---+------------+---------+
| A | B |  formula   | result  |
+---+---+------------+---------+
| 1 | 2 | ={1,2;3, } | #ERROR! |
| 3 |   |            |         |
+---+---+------------+---------+

which will result in an #ERROR!.
Because you do not have simple numbers but handle queries, you get

Error
In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

Functions used:

Custom Functions not working for Google Sheets Addon

I created a GSuite Editor Addon project in Clasp and Typescript. I can test my code via the Script Editor->Execute->Test as Addon and select a Sheet. That works and the addon menu that I defined in the onOpen function appears.

However, all the Custom Functions that I defined in the project are not available from the Spreadsheet. For example, when I type in =double(2), I simply get an unknown function error. But, when I copy the Custom Function to the Spreadsheets’ local scripts (Tools->Scrip editor), it works just fine.

Is there any issue with Custom Functions inside addons?

How to select multiple individual cells on Google Sheets on my Android mobile phone?

I use this feature all the time…

Having opened your sheet on Android, simply tap the cell you wish to access

enter image description hereand at the bottom of the screen you will see the place where you can Enter text or formula. Once done, tap the tick ✓ (right of screen at the end of the entry panel)

enter image description here

and then tap the tick ✓ at the top left hand side of the screen and the job is done.

enter image description here

You can only enter data in one cell at at time but if you are wishing to select a number of cells (for example to edit colours, formatting cell ranges etc), use the corners of the cell selected and drag to open a range (but you cannot enter data that way nor can you, for that matter, in a browser. Neither can you you cannot select multiple unconnected cells.