[HELP] Google Sheet App Script Support for automating manual updation of data

[Abhishek Pandey]

Dear All,

I am working on Google sheet wherein I have to manually update the data every day based on a specific condition. The task was easier until couple of weeks ago. However, recently the daily update includes updating around 200 data by copy pasting approach. This takes most of my time and I am looking to automate this using app script.

Following is the scenario:

There are three different spreadsheet files, each having around 10 sheets. I have to find a unique Product ID in all these files and sheets. Once the Product ID is located, I have to update the Status (approved/pending/failed) and change the date to today's date.

For example,

Product ID is PTR129867453. Once this is found in any of the sheet, I have to update column Daily Status (approved/pending/failed) and Date column as today's date. Currently, I manually search the Product ID and then update it.

I want to see if it is possible to automate this wherein I could parse an array of Product ID and apply a condition that if Product ID is found, change Daily Status = Approved/Pending/Failed and Date= Today's date. This condition should keep on running for all the Product ID while updating the Product ID found whereas returning the list of Product ID which are not found in either of the sheets.

I do not know the code for this and how to obtain the unfound Product ID.

I kindly request the entire community here to please help me in getting it automated. I would really appreciate your help and look forward to your kind response.

Thanks and Regards,

Abhishek Pandey

[cbmserv...@gmail.com]

Hi Abhishek,

 

This is quite possible to do with AppsScript as long as you have edit access to all the spreadsheets. It would depend on how easy it is to search through the various spreadsheets and sheets or if there is a direct method to index into them.

 

Are you a programmer and wishing pointers on how to set it up or are you looking to hire a consultant to do it for you?

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/aa7c6426-38a6-4ed6-86ca-2ae35e343b4en%40googlegroups.com.

[Abhishek Pandey]

Hello Sir,

As per your trailing mail, I do have edit access to all the spreadsheets. Also to search the sheets, the only indexing used or which is unique across all sheets is the Product ID. It is not repeated at any place.

I am a programmer but a beginner with Apps Script. I have not programmed on Apps Script. Hence, I am looking for your help if you could guide suitable resource or approach I could refer to code this. As this is my individual effort and initiative to code this, I do not have the mandate from the company to do this on a consultancy basis.

I kindly look for your guidance on the above issue.

Thanks and Regards,

Abhishek Pandey

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/G8ytCwWevQo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/004601d8c86b%248bc132e0%24a34398a0%24%40gmail.com.

--

[cbmserv...@gmail.com]

Ok .

 

Not knowing the structure of the spreadsheets and how you want to do the update. I would suggest you do the following:

 

1. Start with input
1. How is new data entered? If manual, you may be able to do an on Edit trigger to collect the new data to use
2. If it is a download of data, you may just have to manually trigger the update or do a time based trigger if you know how to find the spreadsheet with the new info.
2. Data seems to have multiple entries, so you will need to setup a loop to go through all the items
1. Each loop will need to:

                                                    i.      Grab product it

                                                  ii.      Search through spreadsheets for that product id

                                                 iii.      Update relevant info in correct sheet

 

 

 

here is an article you can get some ideas on how to search a spreadsheet.

 

https://stackoverflow.com/questions/44523379/searching-a-spreadsheet-using-google-apps-script-returns-no-data

 

It gives you a starting point to look for the particular product id.

 

Start on it then share what code you have put together as well as some samples of the spreadsheets and we can give you more pointers.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO3hXNoNHOdZrfjPFCoQNXcZ8i4x9PZx4N1Da%2BOW00-TTcUPrA%40mail.gmail.com.

[Abhishek Pandey]

Dear Sir,

Thank you for your prompt response and guidance.

I will do the steps as you have mentioned and also refer to the link on finding the product id.

In case I am stuck with anything, I will definitely reach to you along with the sample spreadsheets and the process of how it is being updated.

I really appreciate your help and guidance on the same.

Thanks and Regards,

Abhishek Pandey

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/006501d8c86e%24c61b3820%245251a860%24%40gmail.com.

[Abhishek Pandey]

Dear Sir,

I tried working on the code based on the idea you shared and the resource link you added in the trailing mail. However, I am unable to get the output as needed.

I am attaching the links to the sample files for your reference please help me.

There are three project worksheets which have month-wise sheets as follows:

Project 1 [Link]

Project 2 [Link]

Project 3 [Link]

Now, we daily get a sheet of data with status and date which can be found here. This sheet has the Product ID (POID), current status and date when the sheet is received. This information needs to be updated for the POID found in any of the three projects (1,2,3). Each POID is unique and can be found in only one project sheet. We have to manually find the POID from this sheet in the project worksheets and update the status and date. This requires to be done more than 200 times and hence if it can be automated, then it will be really helpful.

Please let me know your guidance on this.

Thanks and Regards,

Abhishek Pandey