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

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

Extract data from web with google script

I’m trying to use the function below (source) to avoid using importxml in Google Sheets:

function importRegex(url, regex_string) {
  var html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html.length && regex_string.length) {
      var regex = new RegExp( regex_string, "i" );
      content = html.match(regex)[1];
    }
  }
  Utilities.sleep(1000);
  return content;  
}

I want to extract stock prices and day change from Yahoo Finance, for example from: https://finance.yahoo.com/quote/TSLA/, but I can’t get the regex_string right. I have tried several strings but none seens to work. Some one can help me please?

=importRegex("https://finance.yahoo.com/quote/TSLA/", "<span class=""Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)"" data-reactid=""32"">(.*)</span>")

Go to Source
Author: Caseyblack999

Preserve leading zeros in a Google Sheets CONCATENATE function

I am trying to create about 50 cells that have number combinations which look like variations of this:

01:05:39

I am using a RANDBETWEEN function to generate three pairs of numbers (e.g. =RANDBETWEEN(0,59) in cells A1, B1, and C1, and then using CONCATENATE (e.g. =CONCATENATE(A1, ":", B1, ":", C1) ) to put them together in another cell. I’ve tried a number of different methods (including Plain Text formatting on the RANDBETWEEN cells, pasting values only into a different column and using those values, wrapping parts of the formula with a TEXT function, custom formatting with 00:00:00 on the CONCATENATE cells, etc.), but my resulting cells are not retaining the leading zeros. They end up looking like this:

1:5:39

If you’re wondering why I’m trying to do this, I’m a product designer and I need to make some realistic looking timestamps for a mockup. Anyone know how to solve this? Maybe I’m overcomplicating things somehow and there’s a much easier way?

Go to Source
Author: Peter Bentley