google sheets – How do I transform raw time series data to a form that would suit a time series chart with lines named for values in the data itself?

This the data I have –

A           B            C
12/15/2019  Apple       37
12/15/2019  Banana      11
12/15/2019  Carrot       1
12/15/2019  Damson       4
12/15/2019  Fig          1
12/16/2019  Apple       48
12/16/2019  Banana       6
12/16/2019  Carrot       3
12/16/2019  Eggplant     1
12/16/2019  Fig          5

It goes on for years. I have missing data points – See Damson and Eggplant don’t exist for both dates.

I don’t know if Google SpreadSheets can turn that data into a time line chart as is, of wether I’d need to transform into a table with all of A-F represented as columns on their own and missing entries set to zero explicitly.

I could probably write some Python to make a CSV representation as described, like that (even without the fancy libraries), but I wonder if it is possible in a s/sheet tech.

Is there a simple formula? What is the name for this transform?

google sheets – Pull data from one tab to the other


Your privacy


By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.




javascript – Is there a better way to copy dynamic data from one sheet to another in google sheets script?

I’m pulling data in from an API and putting it in the top two rows of a sheet. This script is to copy the data from the “pull” sheet into a daily sheet. The rub is that the data may change and I want to dynamically copy the data over; i.e. if the column header exists, put the new data in the bottom row. If the column header does not exist, create a new column and copy in the data into the correct row.

The script feels cumbersome and not reusable. Is there a better and/or more generic way to do this?

 function runCopyToDailyScript(){
var souceSheetName = "AAVE Data Pull";
var targetSheetName = "AAVE Daily" ;
let ss = SpreadsheetApp.getActiveSpreadsheet();           // get active spredsheet
let sourceSheet = ss.getSheetByName(souceSheetName);
let targetSheet = ss.getSheetByName(targetSheetName);
var nextTargetRow = targetSheet.getLastRow() + 1. // where the next row of data is going

// get the data in top two rows of source sheet
var sourceRange = sourceSheet.getDataRange();  // get range of all data on source sheet
var sourceData = sourceRange.getValues(); // get the values of all the data in the source sheet
var sourceArray = transposeArray(sourceData); // transposeArray from genearal functions

// target data
var targetHeadersRange = targetSheet.getRange(1,1,1,targetSheet.getLastColumn());  // get top row of daily data
var targetHeaders2DArray = targetHeadersRange.getValues();  // array of headers within an array
var targetHeaders = targetHeaders2DArray(0);  // creats an array of the headers

/** go through source (the data pull) headers and see if they are in target sheet */
sourceArray.forEach(element => checkDailySheet(element));  // goes through each pair of source data. checkDailySheet is another function

function checkDailySheet(subArray){
  let theIndex = targetHeaders.indexOf(subArray(0)) // returns the index of the key in the pair in the target sheet if it exists

  // test to see if the the column head is in the target sheet
  if (theIndex != -1) { 
          let targetCell = targetSheet.getRange(nextTargetRow, theIndex + 1, 1, 1) // nextTargetRow is constant declared above
          targetCell.setValue(subArray(1)) // sets the last row in the target column to the source value
          
  } else {
          /** find the next avail column in target range */
          targetHeadersRange = targetSheet.getRange(1,1,1,targetSheet.getLastColumn() + 1);
          targetHeaders2DArray = targetHeadersRange.getValues();
          targetHeaders = targetHeaders2DArray(0); // array with the target headers + one extra
          let targetHeadersLength = targetHeaders.length;

          let newTargetHeaderCell = targetSheet.getRange(1, targetHeadersLength, 1, 1);
          newTargetHeaderCell.setValue(subArray(0)); // set the next collum header with the missing key

          let targetCell = targetSheet.getRange(nextTargetRow, targetHeadersLength, 1, 1) // nextTargetRow is constant declared above
          targetCell.setValue(subArray(1)) // sets the last row in the target column to the source value
          
   }; // end of else
  }; //end of CheckDailySheet
}; // end of runCopyToDailyScript()

Sheets or Excel – sum values in column a for each unique date and time in col b

If your times in Col A are hour-minute as shown (i.e., no seconds):

=QUERY(A:B, "Select A, SUM(B) WHERE A Is Not Null GROUP BY A LABEL SUM(B) ''")

This assumes your values start in A1:B1 (i.e., that you have no headers).

A longer but more flexible and easily transferable version of the above:

=QUERY({A:B}, "Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL SUM(Col2) ''")

google sheets – Using Cell Value as a Worksheet Name in Formula

I wanted to use a cell’s content as a sheet name, i.e.

A1 A2 A3 A4
South North East West

and use the cell values (South, North, East, West) to reference cells from sheets called South, North, East, West like this:

=South!A1
=North!A1
etc. . .

I wanted to use this to count all instances of TRUE in column A. My current implementation was inspired by a solution for Microsoft Excel and uses the INDIRECT() function, but it doesn’t seem to work for Google Sheets:

=ARRAYFORMULA(SUM(IF(INDIRECT(A1)!$A:$A = TRUE, 1, 0)))

Google Sheets: Counting data from a moving range

So, I’m not amazing with Google Sheets, I’m ok but nothing crazy. I’m looking for a formula or method of counting data from the last 5 entries. These entries are added 3 times a week and the range would need to move automatically for this to function properly.

First off, is this actually possible?
Secondly, if it is, how would I go about counting 3 different criteria’s from a moving range?

Here’s a link to the example spreadsheet I created.
https://docs.google.com/spreadsheets/d/192fZVzqq-ML3YplTSKUlVNuEMXCAfePE1IYzYPikukQ/

There are 5 “people” and 13 entries for each person which is either “Yes”, “No”, or “Ab”. The actual entry itself is not important but I’d like to be able to count, say, the last 5 entries.

If anyone here knows how to do this, or if this is even possible, please help me 😀

-Thomas

google sheets – Return every column header that contains a value

I’m trying to return the header of every column in which a value appears, and put these headers in one cell separated by commas e.g. if “xyz” is contained within columns titled Column1 and Column7, then this formula would return “Column1, Column7” in the cell.

  • The headers of the column are in Row H4:AF4

  • The data is underneath, in H5:AF99

  • The value I’m looking up sits in cell E1

At the moment this formula is working for some values, but not all:

=JOIN(", ",QUERY(TRANSPOSE(H4:AL99),"Select Col1 where "&JOIN(" or ",ARRAYFORMULA("Col"&(COLUMN(H4:AL4)-COLUMN(H4)+1)&" = '"&$E$1&"'"))))

Can anyone explain why this formula works for some of my lookup values but not all?

google sheets – Using Index + filter in Spreadsheets

I would appreciate some help with this spreadsheet issue. I am trying to automate column B to find the next shipment in the restock row and column C to find that same shipment from the same restock row and by using offset to go to the date cell once that next shipment (950) is found. (The table on the right.)

My problem that the formula that I used with the filter function in C2 is giving me an error. However, if in that same formula, I remove the filter (which is set based on date conditions and should start based on today’s date in any given date) and I set the range manually, it works fine (see cell C5)

Here it is the link to my sample (here)

Thanks for any help in advance!

enter image description here

google sheets – formate custom cell output from horizontal to vertical cells

I have the following table setup:

Date Time Name1 Name2
01.01.0001 15 Bob Emily
02.02.0002 21 Bob2

and I want to transform this into this:

Date Time Names
01.01.0001 15 Bob
Emily
02.02.0002 21 Bob2
03.03.0003

Without using any custom google script functions.
Is that possible?

How to stop users from unfreezing rows on Google Sheets?

I have some frozen rows but other users (who must have edit permissions) are inadvertently unfreezing them. I have tried adding protection to the rows so they cannot edit them, but they can still unfreeze them. Is there any workaround for this?