Create a script for Google Sheets (apps)

Good day
Here is the task:


-----
There is a table https://docs.google.com/spreadsheets/d/1LlVWVzv2c_WD7LTdQXFnW7kQsniLyNdXma8ihVDg9bc/edit?usp=sharing


In it, there are tabs Auto, Broad, Exact, and Compet which are examples of advertising campaign structures for the product HB-UM43 on Amazon.


Auto - campaign type with automatic targeting.
Broad - campaign type with broad match targeting.
Exact - campaign type with exact match targeting.
Compet - campaign type with competitor ASIN targeting.


Task: create an App script for Google Sheets (not Python) that will generate similar campaigns for other products on tabs Auto1, Broad1, Exact1, Compet1. Also, on the All_together tab, it will collect all three newly created advertising campaigns under one header.


For new campaigns, variables on the Data tab will be used:


1) Daily budget - has one value and will be used for all campaigns in column U "Daily budget", in cell U2. Also, similar values need to be added in other cells in row 2 (further instructions will be provided).


2) SKU - product name has one value and will be used for all campaigns in the columns:
- Campaign ID column - this is the ID of the advertising campaign, we will form it in the format SKU-+campaign type. These values are present in each row of the campaign structure in this column.
- Ad group ID column - this is the ID of the ad group, we will form it in the format SKU-+campaign type. These values are present in each row of the campaign structure in this column except the row where Campaign ID is present.
- Ad group name column - this is the name of the ad group, we will form it in the format SKU-+campaign type. This value is present once in the row below where the Campaign ID value is present.
- Campaign name column - this is the name of the campaign, we will form it according to the formulas SP-A-(SKU value)-/Category (for Auto campaign), SP-B-(SKU value)-/Category (for Broad campaign), SP-E-(SKU value)-/Category (for Exact campaign), SP-C-(SKU value)-/Category (for Compet campaign). You will also find the Category value on the Data tab. Campaign name is used once in the same row where the Campaign ID value is present.
- SKU column - product name or number is used once in column V


3) Basic bid - the value of the base bid for targeting and is used in the "Ad Group Default Bid" columns (once in the row where Entity value is Ad Group). Also in the "Bid" column where in the row the Entity value is Product targeting (for Auto and Compet campaigns)
The value in the "Bid" column changes depending on the campaign:
- For Auto campaign, it equals the base bid
- For Compet campaign, +0.1 to the base bid


3.1) Bid - the bid value for each keyword in Broad campaigns (where in the row the Entity value is Keyword for Broad and Exact campaigns). For Exact campaign, +0.15 is added to this value.




4) Keyword text - the value of keywords, the quantity can vary for different products (in example 1). Accordingly, there will be more necessary rows in the structure. Used in Broad and Exact campaigns. In these rows, Entity equals Keyword. And in the Match type column, the appropriate match type value Broad or Exact should be present.


5) Negative keyword - these values come in rows after the Keyword text values. The quantity can also vary, from 0 to 1000 (in example 1). Used for Auto, Broad, and Exact campaigns. In these rows, Entity equals Negative keyword. And in the Match type column, the value should be Negative phrase.


6) Asins - competitor ASIN values, the quantity can vary for different products (in example 1). Used for Compet campaigns, added in the row where Entity equals Product targeting in the following format asin="B07VMXG354" - thus, quotes are added around the ASIN and asin= is added in front.


Additional details:


1) For the Operation column in each created row, give the value "create".


2) The Start date value is used once in the row where Entity value is Campaign. The Start date value should be the date of the day the script is launched. Format 20240502.


3) For the Targeting type, State, Bidding strategy columns - use the values and placement of these values similar to our examples in the structures in the Auto, Broad, Exact, Compet tabs.


Additional script operation details:
Each time the script is run, it should delete previously created structures on the Auto1, Broad1, Exact1, Compet1, All_together tabs if they exist. In other words, do not accumulate data.