Question

How do I automate copying data between Google Sheets with different column arrangements?


I want to create an automation, every time new spreadsheet get uploaded in google drive it gets the data from the newly updated spreadsheet and copy the data of columns to another sheet, the destination sheet has different column arrangement than source spreadsheet.

Here is my workflow look like, I was trying different options but I feel stuck right now.


Any help in this regard will be highly appreciated.
 


5 replies

Userlevel 7
Badge +11

Hi @nadeem, welcome to the Community! 🎉 

If you’re wanting to copy across multiple rows of updated data from one spreadsheet to another then you’d potentially want to use Lookup Spreadsheet Rows and Update Spreadsheet Row(s) actions instead for steps 3 and 4 of the Zap. Those actions will be able to search for and update multiple rows, unlike the existing Lookup Spreadsheet Row and Update Spreadsheet Row actions that are only capable of searching and updating a single row. 

Can you try switching those actions instead to see if that solves things? 

Eager to help get things up and running so please do keep us updated on how you get on - happy to assist further! 🙂

Hi @SamB, I see, actually both sheets has different arrangements of columns, I want to copy the data of 1 column and add that in destination sheet, for example, in source sheet I want to copy data from A7 but that column is on D7 in destination spreadsheet, and B7 is E7 on destination, make sense?

If there is way of doing it, I would highly appreciate that.

Thanks

Userlevel 7
Badge +11

Thanks for getting back to me @nadeem

It’s possible to map the necessary fields from one spreadsheet column to a completely different column in the Update Spreadsheet Rows action. However, if the arrangement of the columns in the spreadsheet that’s triggering the Zap will be different every time the Zap triggers then this would likely result in the wrong data being added into the wrong column. 

So if the spreadsheet that triggers the Zap will always have differently named or arranged columns then perhaps you might want to explore setting up this workflow in Zapier Central. It’s still in Beta at the moment but potentially you could to build the same workflow but use AI to determine which values to map to which columns on the second spreadsheet. You can learn more about how to set up Zapier Central here: Build a bot in Zapier Central
 

Hope that helps to get you pointed in the right direction. If you run into any issues on that or have further questions just let us know!

Hey @SamB, sorry for steering you in the wrong direction, column arrangement will not be changed either in source or destination, like I said in the previous message A7’s data from source sheet will be mapped to D7 in destination spreadsheet and B7 will be mapped to E7 in destination spreadsheet, I hope it clears the confusion.

 

Userlevel 7
Badge +11

Ah I see. Sorry for the misunderstanding on my part @nadeem. It sounds like you’re instead looking to update the same cells in row 7 specifically each time the Zap runs.

If that’s the case, then you’d need to set the Get Many Spreadsheet Rows action to only return the data from row 7:

01901ccd19a16eeafe2b10192a29eb56.png

Then you can remove the Lookup Spreadsheet Row action as it won’t be needed and just set the Update Spreadsheet Row action to update the destination spreadsheet with the relevant data by mapping the necessary fields from the Get Many Spreadsheet Rows action. Also, in that Update Spreadsheet action you’d need to select row 7 in the Row field so that whenever the Zap runs only row 7 is updated. So for Column D on the destination spreadsheet you’d select Column A’s field, for example:
a831693161fff8d00b5802747b1a8aa7.png

Do let me know if I’ve further misunderstood or if you run into any issues on that, keen to ensure you’re all set! 🙂

Reply