Skip to main content
Question

How do I update data in new Google Sheets with FreeScout messages?

  • 4 July 2024
  • 5 replies
  • 19 views

Hey, everybody.

I am linking Freescout and Google Sheets, my task is to create and manage the client's table (not to run from CRM to tables and back again) using messages in the dialog box.

I already can:
create a table with the name of the client, by the message in the "Create table" dialog I can create a sheet with a name equal to the current month and year and prepared column names. 
And I'm stuck on trying to put in these columns data from the dialog, which I have unpacked, the problem is that the ID of the sheet is not dynamic, like the ID of the table, but static, which means that next month, when a new sheet will be created, the data from the dialog will continue to be added to the old sheet, which I choose. How can I win?

 

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

SamB
Rank
Userlevel 7
Badge +11

Hi @yabloky, welcome to the Community! 😁

Assuming the worksheet found by the Find Worksheet action is the one you want to add a new row to then if you map the ID of the worksheet from that Find Worksheet action then that should ensure that the correct worksheet will have a new row added to it.  

Alternatively, for spreadsheets that only have a single worksheet their ID will be always 0. So you could use the Custom value option and type in 0 for the worksheet, for example:
19fdc73ff8249fe579964cf47c5fd065.png

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

yabloky

Hi, thanks for your willingness to help. But unfortunately I didn't understand how to realize my task.

Let's go through the steps again.

I create a table, in the table I create a sheet with a name that corresponds to the current date, for example June 2024 and this sheet has a static ID. 

And I need to get a dynamic or somehow identify the last created sheet. That at the last stage to identify the sheet of the current month and make changes to it. How can I define a sheet created exactly in the month of editing? If I select from the dropdown task where I create this sheet, I can only select the static ID of the created sheet of this stage.

SamB
Rank
Userlevel 7
Badge +11

Thanks for getting back to me @yabloky. 

It looks like you have got a Date/Time Formatter action which I’m guessing reformats the current date to output just the month and year, is that correct?

If yes and the worksheets that the Zap creates always follow the same format of month year (e.g. June 2024) then in the Find Worksheet search action you’d select that reformatted date value in the Title field on the Find Worksheet action to find/create the relevant worksheet:
0d8ec61c185e241f078d0bf7d51af2c9.png

Then in the Create Spreadsheet Row action in the worksheet field you’d use the Custom value option and select the ID of the worksheet that was found or created by the Find Worksheet action (step 6 of your Zap), for example: 
f14bdfa38f9d2e633389e940e0c798cb.png

This would dynamically select the worksheet and ensure that the spreadsheet row is added to the relevant worksheet for the current month. When selecting a field from a previous trigger or action step the value will dynamically change depending on what value is contained in the selected field. 

Hope that helps to clarify. If you run into further trouble on this or have any additional questions just let me know! 🙂

yabloky

Yes you are right, in one step I define the date for the current moment and format it in MMMM YYYYY format, in the other step I create a sheet in the table, with the name of the title, which I pull from the previous step, then a sheet with a static ID is created.

The system is new for me and my English is not the best, I can ask you to create for me for an example the necessary zap, which will be the data that I sparse - insert into the same-named columns in the sheet, the name of which corresponds to the month in which the work occurs. I.e. every month a new sheet will be created.

June 2024
July 2024
August 2024

And all orders that will be created, will be added to the sheet, which is now a month. This is the problem I encountered, how to select the sheet with the current month. Would you mind if I create the missing zap? Don't be afraid to break something or mess up - I'm just testing the capabilities of your service and whether I can use it for a fee.
Thank you

SamB
Rank
Userlevel 7
Badge +11

I’m sorry for the delay in my reply here @yabloky.

I can’t create the Zap for you or access your account to make changes to any of your Zaps. If you’d prefer someone to build it for you I’d suggest reaching out to one of our certified Zapier Experts here: https://zapier.com/experts. Otherwise, I’m happy to continue assisting on this here! 🙂

If your Formatter action is using the {{zap_meta_human_now}} command (see: Insert the time your Zap runs into a field) to convert the time the Zap runs into a month and year value then that will ensure the the Formatter action always outputs the current month and year that the Zap runs.

For example a Formatter action setup like this:

6937466162716613159f9c3676247c0d.png

It would result in an output of July 2024 if the Zap runs on any day within the month of July, 2024:
5d92a91fdf003fc5d4177f4d26eb3e08.png

If the Zap runs in August this year, the Formatter action would then output a value of August 2024. If it runs in September, it would output September 2024 etc.
 

And if that Output field containing the reformatted date is selected in the Find Worksheet action, then that will ensure that the Find Worksheet action always find the worksheet for the current month. And in that same Find Worksheet action if the Create Google Sheets Worksheet if not found? option is enabled that will ensure that if a worksheet for the current month isn’t found, the Zap will then create a worksheet for the current month:
2379be5b18ff3d5c58a05956f895886e.png

By selecting the ID field from the Find Worksheet action, in the Create Spreadsheet Row action that will ensure that the spreadsheet row is added to the worksheet for the current month:
596ab0dde0537e9e0b47b5229996c8df.png

Let me know if you run into any issues or further questions at all on this, keen to ensure you’re all set!

Terms & Conditions