Hi everyone,
I've written all of the scripts I need but I'm looking to futureproof them.
For context, I do Amazon Ads and I download Amazon's reports, filter columns to find the rows I need, do what I need to, then re-upload to apply the changes.
The bulk of my scripts involve filtering data by columns, e.g. Column B is 'Entity', R is 'State', O is 'Bids', etc.
The problem is that Amazon is in control of these report structures and if they decide to add new columns or remove old ones, then my scripts will break because columns will be shifted around, and it will be a PAIN to have to go in, find each Column reference by letter, then change each to the new Column letter. This is a dreadful thought, lol.
The only way I can conceptualize fixing this is to make an array of all column headers. Then, for each column header, search all column headers, and set each column header string to equal the cell reference where it's found. Then I'd need to be able to reference the respective ranges using the header strings.
E.g.
Assuming the column header 'Entity' is B1:
The script will make an array of all column header strings (from A1 to the last column header)
The script will then loop through the array, one column header string at a time, and search for each string in the entire column header range (i.e. from A1 to the last column header)
For each column header, it will set the respective string to equal the cell address where it's found
e.g. When looping through the array, and i = 2, it will search for 'Entity' in all the column headers, then when it finds it, it will set the string 'Entity' to equal the address, which in this example is B1 (well I'll have to use the row number and column number referencing method instead).
My huge assumption is that instead of referencing columns using their letters throughout my scripts (e.g. 'B1'), which Amazon can change at any time, I can use strings (e.g. 'Entity') instead to refer to the range to do my filtering, copy-pastes, etc. This way, even if Amazon changes column headers, my script will find and set as needed to adapt.
I tried something like the above a few weeks ago but it didn't work (and I deleted my attempt which I now regret). Any advice on how to approach this is appreciated, whether I'm on the right track, or you want to point me in the right direction. Hate to ask someone to write the code for me but I'd appreciate all the help I can get. Thanks!
I've written all of the scripts I need but I'm looking to futureproof them.
For context, I do Amazon Ads and I download Amazon's reports, filter columns to find the rows I need, do what I need to, then re-upload to apply the changes.
The bulk of my scripts involve filtering data by columns, e.g. Column B is 'Entity', R is 'State', O is 'Bids', etc.
The problem is that Amazon is in control of these report structures and if they decide to add new columns or remove old ones, then my scripts will break because columns will be shifted around, and it will be a PAIN to have to go in, find each Column reference by letter, then change each to the new Column letter. This is a dreadful thought, lol.
The only way I can conceptualize fixing this is to make an array of all column headers. Then, for each column header, search all column headers, and set each column header string to equal the cell reference where it's found. Then I'd need to be able to reference the respective ranges using the header strings.
E.g.
Assuming the column header 'Entity' is B1:
The script will make an array of all column header strings (from A1 to the last column header)
The script will then loop through the array, one column header string at a time, and search for each string in the entire column header range (i.e. from A1 to the last column header)
For each column header, it will set the respective string to equal the cell address where it's found
e.g. When looping through the array, and i = 2, it will search for 'Entity' in all the column headers, then when it finds it, it will set the string 'Entity' to equal the address, which in this example is B1 (well I'll have to use the row number and column number referencing method instead).
My huge assumption is that instead of referencing columns using their letters throughout my scripts (e.g. 'B1'), which Amazon can change at any time, I can use strings (e.g. 'Entity') instead to refer to the range to do my filtering, copy-pastes, etc. This way, even if Amazon changes column headers, my script will find and set as needed to adapt.
I tried something like the above a few weeks ago but it didn't work (and I deleted my attempt which I now regret). Any advice on how to approach this is appreciated, whether I'm on the right track, or you want to point me in the right direction. Hate to ask someone to write the code for me but I'd appreciate all the help I can get. Thanks!
