#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

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

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

Simplify app script for google sheets

Hi anyone knows how I can simplify this code?
This is working, however it takes time to update the datetime column

function onEdit(e) {
var row = e.range.getRow();
var ws = “Consults Form Responses”;
if(row > 1 && e.source.getActiveSheet().getName() === ws)
{addDateAnswered(e);}
AllFormat();
}

function addDateAnswered(e){
var row = e.range.getRow();
var validity = e.source.getActiveSheet().getRange(row,14).getValue();

if(validity == “VALID” || validity == “INVALID”)
{setDateAnswered(e);}
else if(validity == “DUPLICATE” || validity == “NFA”)
{e.source.getActiveSheet().getRange(row,17).setValue(validity)}
else
{e.source.getActiveSheet().getRange(row,17).setValue(“PENDING”);}

var AllRange = ws.getRange(“A2:P”);
AllRange.setVerticalAlignment(“middle”);

var AllCenter = ws.getRange(“A2:A” && “F2:F” && “J2:K” && “M2:M”);
AllCenter.setHorizontalAlignment(“center”);

var TestLeft = ws.getRange(“O”);
TestLeft.setHorizontalAlignment(“left”);
}

function setDateAnswered(e){

var row = e.range.getRow();
var col = e.range.getColumn();
var TimeStamp = Utilities.formatDate(new Date(), ‘GMT+8:00’, ‘MM/dd/yyyy hh:mm:ss a’);

if(col === 14 && row > 1)
{e.source.getActiveSheet().getRange(row,17).setValue(TimeStamp);}
}

Go to Source
Author: KB John

Query or filter the data when using date time format

Googlesheet: https://docs.google.com/spreadsheets/d/1hO_KatnkP58wuF6I7kwtlGgCI6xM6ubopgCwuYMXXIU/edit?usp=sharing

Sheet2

In cell A2 I have:
=QUERY(Sheet1!A1:Q, “Select * where B >= datetime ‘”&TEXT($A$1,”MM-DD-YYYY HH:mm:ss”)&”‘”)

In cell A1 I have:
6/17/2020 10:53:22 AM

Sheet1:
Column A (user) and B (date and time)
User1 6/17/2020 10:53:22 AM
User2 6/17/2020 10:53:22 AM
User3 6/17/2020 11:10:55 AM
User4 6/17/2020 11:33:57 AM
User5 6/24/2020 10:47:34 AM
User6 6/25/2020 4:50:39 AM
User7 6/24/2020 4:00:24 AM
User8 7/4/2020 5:44:25 AM
User9 7/4/2020 7:08:29 AM
User10 7/4/2020 8:49:24 AM
User11 7/3/2020 7:36:46 AM

The expected output in A2 Sheet2 is:
User1 6/17/2020 10:53:22 AM
User2 6/17/2020 10:53:22 AM

but the query I used outputs all of the data from Sheet 1.

Hope someone can help me…

Go to Source
Author: KB John

Automatically Increase Document Number/Cell Number on open

I would like to be able to click on something which will open a new google sheet based on a template. I would need cell A1 to contain a number which increases by 1 every time the template is opened. Ideally I would like the title to match this new number.

I am working on developing a purchase order form and want to make sure that no matter who opens the template sheet, they will be given their own PO number in order of when they opened it to avoid multiple people using the same number.

I have limited scripting experience so i have been performing a lot of google searches and trying to map out how to ask this properly. I have tried a macro that makes the title the same as cell A1 but no luck. I still have no idea how to automatically generate an incremental number for the title based on the time it was clicked.

Go to Source
Author: Oliver H

insert png image to google sheets or other cloud based structure in python to be used in tableau

i m pulling this sensor data and have an image (heat map) that i want to put in some cloud based location where i can easily pull it from tableau. the problem – tableau has preset web connectors so i need to be able to write it and pull it from one of those places.i m working from a remote computer with the image data and tableau is on my personal computer so thats why i need storage on the cloud that is acceptable to tableau

Go to Source
Author: vanetoj

How to show thumbnails of images stored on Google Drive on Google Sheets?

I have a Google spreadsheet that contains hyperlinks to files stored on my Google Drive using a HYPERLINK formula.

Opening the spreadsheet and hovering the cursor over the link, I can see a thumbnail preview and that’s pretty neat (if you feel lucky, try yourself on this example sheet)!

thumbnail preview

However, this doesn’t happen for my collaborators, and indeed if I open the same spreadsheet with another account with similar access rights (on the spreadsheet and on the image) I don’t see any thumbnail preview.

You can try the example I took the screenshot of here. Is this feature currently available on selected accounts only, or am I missing something?

Go to Source
Author: Davide Fiocco

Get Table Name With Apps Script

I have two tables, namely Table A and Table B. Each table I want to identify the name of the table in column C
matches the table names that have been defined in column A.

The script I’ve made is like this:

tableName () function {
  let ss = SpreadsheetApp.getActiveSpreadsheet ();
  let cColumn1 = ss.getRange ('C3: C7');
  let cColumn2 = ss.getRange ('C11: C15');

  let tableName1 = ss.getRange ('C2'). getValue ();
  let tableName2 = ss.getRange ('C9'). getValue ();

  cColumn1.setValues   (tableName1);
  cColumn2.setValues   (tableName2);
}

But my script is still manual, we have to specify the start line and end line of the table.
To identify the name of the table I’ve ever made with a formula, so with this formula, we don’t need to specify the position
the name of the reference table.

= ARRAYFORMULA (IF ((B: B = "") + (B: B = "Name") ,, VLOOKUP (ROW (A: A), IF (LEN (REGEXEXTRACT (A: A, "TD+") ), {ROW (A: A), REGEXEXTRACT (A: A, "TD+")}, 0), 2)))

This formula is very heavy if the amount of data is large.

My Spreadsheet.

Go to Source
Author: Lutfi Creativesys

Synchronize time among several concurrent users

I’m using Google Sheets to come up with a quick Jeopardy-style game with an improvised buzzer. All players type in the timestamp shortcut (Ctrl+Shift+0) into a cell and the lowest value is presumed to buzz in first, but we’ve realized that some people are a few seconds, even minutes, ahead (or behind). We’ve diagnosed that it’s related to each person’s time set in each’s OS.

Is there a way to sync times through the Sheets app? Or at the very least determine who input first?

I’ve tried to remedy with a series of nested array formula with if and count statements, but to no avail. Any help would be appreciated. Otherwise I’m going to set my OS’s time to 1899 and smoke everyone.

Go to Source
Author: Rudy A