office365 – How to link external excel workbook in office 365 excel online? I linked two sheet, but it is not working for my colleagues?

In my organization, we have a master tracker (MT.xlsx) maintained by the group manager in his OneDrive folder. Some data from it needs to be shown to other people in the organizations, and hence we have created another tracker (BO.xlsx) which is in my OneDrive folder.

Everytime we have to copy paste the data and this is laborious task. Hence I linked the two sheets using a simple workbook link formula. But this works only when the sheets are open in my laptop and in desktop app and they do not work in Excel browser version.

When my colleagues update MT.xlsx and if both sheets are not open in my laptop, BO.xlsx does not get updated. Can anyone please help me with how can we link the two sheets so that BO.xlsx can be updated without it being open in my laptop?

google apps script – Copy data from one sheet to another at the end of every month

I have a google sheet where I track the performance of multiple campaigns.
enter image description here

I want to copy the completion value to another sheet at the end of every month. Each month should have it’s own column. This is how it should look:

enter image description here

Right now, I manually copy paste the completion values into the empty columns adjacent to the relevant month and subtract the previous month’s values to get the increase in campaign completion. Then that % is multipled with the total value to get the amount I accrued for that month.

How can I automate the process so that the data get’s added from the Campaign sheet to a new sheet at the end of every month?

excel – Query in row as well as column in my main sheet

I want a formula which can give answer to my query in row as well as column.
one is my main sheet and another are my working sheet. Rows are name of employees and colums are days of month ie, attendance of employuee sheet(month-wise) of 12 months so i have 12 sheets.
My main sheet is also having same rows and colums but for 365 days (12 months)
Now, what i need is, a formula which can provide me in my main sheet, whether the employee was present on that particular date or not.

How to jump to a specific customer name in a google sheet

I am trying to create a google sheet that lists all payments made for tuition by family. I would like to use only one tab (not create a tab for each family) as I have about 120 families to keep track of.

Any thoughts of the best way to do this without having to scroll forever to get to the specific family I need to look at? Currently I have the families listed in alphabetical order in multiples of 4 going down and then the next set of 4, etc. This is easier when it comes to printing certain sections of the sheet for cross reference (ie not having to scroll down forever to find a name).

Date Family
8/6/20 $150 cc = app fee
8/17/20 $550 ck #2635 = reg, corp
9/10/20 $650 ck #3016 = Aug tuit
10/12/20 $650 ck #4361 = Oct tuit
11/10/20 $650 ck #3027 = Nov tuit
12/16/20 $650 ck #2143 = Dec tuit
1/20/21 $650 ck #2738 = Jan tuit
2/26/21 $650 ck #4622 = Feb tuit

I know how to use the F5 shortcut to get to a specific cell but how do you get to a specific cell by typing in the name of the family? That would help with the whole scrolling issue.

Or if anyone has a better idea of how to record these payments I’d love your help.

android – Is a bottom sheet with no close button accessible?

I’m using a bottom sheet.

On Android, people can use the back button to close it, drag it down or tap outside the area.

On iOS, people can drag it down or tap outside the area.

I’m just wondering if it’s accessible enough. Would a close button / icon be good to have?

Cheersenter image description here

Two Y-Axes in Google Sheet Histogram

I have created a histogram using two columns as series in google sheet. However, the y values of the first column are much greater than the y values of the second one. So, I thought to create two y-axes instead of one. Although I found a solution for other charts here 2-axis line chart in Google Sheets, it doesn’t work for histogram charts since there isn’t a choice for selecting axes for the series in the google sheet chart editor.

Is there any possible way to create two y-axes in google sheet for histogram charts?

google apps script – Update column based on a cell on another sheet

I’ll do my best to describe my problem and what I want to achieve. As it stands, I have two sheets. One called “First Sheet” and another called “Secondary Sheet”. On Secondary Sheet, I’ve used importrange to obtain responses from a Google Form.

I would now like to see if a username from First Sheet, Column A, can be found in the Secondary Sheet, Column B. If it can be found, I would like to update the cell to the right.

For example, if the username at A5 on the First Sheet is found on Column B in the Secondary Sheet, update D5.

This should be written to the cell permanently so I’m looking to use a Google Apps Script only. I hope my explanation was clear.

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()

google sheet : extract number from string by matching the prefix

I have a cell contains this value/text :

T50 EX3 OF2 BE40 BL10 ZLD6 ZLS2 ZRS10 AA1

How to get the number with the ‘EX’ prefix ? in this case i want to extract that ‘3’ from EX3.

Also, this number can be any digit, for example it can be :




How to extract that ‘112’ and ‘00078’ and make it as number 112 and 78 ?


Sheet to videos. What do you think about this? (Need some feedback!)

Hi all,
I hope I’m not bothering anyone with this, but I just wanted to ask you for some honest feedback

My team and I are working on WOXO. A cool video maker that generates videos in bulk. We realized how troublesome and time-consuming video creation really is, so we thought: why not edit by simply adding and customizing videos directly in spreadsheets? And then link social media accs, your drive and re-utilize those assets (pictures, videos)?

I think it could be very useful for those who need to create lots of videos for their clients, designers, marketers and so. But what do you think?

Would you give it a try and let me know in comments?

Here is the WOXO video maker:

Thanks a lot!