CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
tputkonen
tputkonen

Reputation: 5729

Access data of one column in another worksheet

I have a very simple need: in Google Sheets, I need to access all data in column B in another sheet.

=IMPORTRANGE(<reference to sheet>, "B:B")

Doesn't work reliably, and I found out documentation which said open ended cell references must not be used with IMPORTRANGE.

However, I cannot use fixed row index since number of rows in the second sheet is constantly growing.

Is there a way to do this?

Upvotes: 0

Views: 44

Answers (1)

Aditya Mansana
Aditya Mansana

Reputation: 26

Try using the below code in Google App Script editor:

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DestinationSheetname");
var ColumnB = 'SourceSheetName!B1:B'; 
var ImportColumnB = '=unique(query({'+ColumnB+'}, "select * where Col1 Is Not Null order by Col1 asc ",1))';
sheets.getrange(2,1).setValue(ImportColumnB); //In range give the cell from where you want to insert the values in the column (row,col)

Upvotes: 1

Related Questions