• I'm currently using Zapier to parse emails and populate data by creating a new row in google sheets (Sheet Name 'Master' Columns A:J)

• The main problem I'm having when using Zapier & google sheets is that it's very hard to make changes to the sheet without affecting and causing errors with the zap. Zaps can only add rows to the end of the spreadsheet and not any other row. It doesn't like you adding or deleting rows to the spreadsheet, especially in the middle of data. So Zapier advises if you plan on editing a sheet to then Turn Off the Zap first!

• I want to be able to sort the data, make changes and create filters with it while actively working on additional columns. In an ideal world, I could have the Master data populated by zapier and an Active data sheet to make changes, but as you know; Data flows in only one direction. So doing things like importrange, query, arrayformula didn't help because I can't overwrite the values.

• The best / simple solution I can think of is to allow Zapier to create new rows in Master sheet for storage only (ideally hidden sheet). In Master sheet, I could add in Column B cell value being New with zapier. And Using google app script perhaps to automatically copy values of new rows to preferably the top of Active Sheet if not, bottom - (If Active sheet has filters in place will this hinder copy / paste of data or just the visibility of new data?)

So, this brings me back to my title question.

How can I automatically copy values of a new row populated with Zapier in (Master Sheet Columns A:J) to (Active Sheet Columns A:J) in the same workbook with Google sheets?

Thanks

function myFunction() {

// Get Source Spreadsheet
var source = SpreadsheetApp.getActiveSpreadsheet(Master);

// Get Source Sheet from Spreadsheet
var source_sheet = source.getActiveSheet(Active);

CodePudding user response：

Easiest method is just to add

=Master!A1

in Active!A1 and autofill it to Active!A1:J10000 or something. Now you can use filters or array formulas as necessary in Active sheet.