Bulk Shopping Ad Group Creator

Tools icon

Bulk Shopping Ad Group Creator provides a way to bulk create ad groups and product groups in existing Shopping campaigns. It reads ad group settings (such as default bid and status) and product group hierarchies along with bids from a spreadsheet, then creates them in Google Ads. The product group hierarchy must be specified in Google Ads Editor format for Shopping campaigns.

How it works

The script takes the input spreadsheet and processes it row-by-row. The campaign names specified in the spreadsheet must already exist in Google Ads or an error is thrown.

The script follows these rules:

Specified ad group doesn't exist
Ad group is created with specified settings (default bid, status) along with its new product groups.
Specified ad group already exists and does not have product groups (unlikely)
Ad group settings are unchanged, and the new product groups are created.
Specified ad group already exists and has product groups
Ad group settings are unchanged, and its existing product groups are unchanged.

The script appends results to the end of the row it was processing.

Spreadsheet specifications

The script requires the following column headers to be present in the spreadsheet:

  • Campaign: Campaign name
  • Ad Group: Ad group name
  • Max CPC: Default ad group bid if in the ad group row, product group bid if in row with product group
  • Product Group: Product group hierarchy specification in Google Ads Editor format
  • AdGroup Status: Status of the ad group. Can be enabled or paused.

All other columns are ignored by the script. This means that only these settings are copied over to the newly created ad group.

The ad group settings row must come before any product group rows for the same ad group.

If the everything else product groups are explicitly specified in the spreadsheet, the row must come after all product groups of the same level in the same ad group. If not explicitly specified, then they are created with the ad group default bid.

Setup

  • Create a spreadsheet with the above column headers and fill out your ad group and product group details. It should look similar to this sample.
    • Alternatively, you can download an existing campaign with Google Ads Editor export to .csv as a template, make modifications to the .csv, then upload it to Google Spreadsheets. If you do this, make sure the ad groups in the spreadsheet are new (non-existent).
  • Check that the destination campaigns exist in Google Ads.
  • Create a new Google Ads script with the source code below.
  • Don't forget to update the following parameters in the script:
    • SPREADSHEET_URL: URL for the spreadsheet to process
    • SHEET_NAME: Name of the sheet with data to process

Source code

/// Copyright 2015, Google Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

/**
 * @name Bulk Shopping AdGroup Creator
 *
 * @overview The Bulk Shopping AdGroup Creator provides a way to bulk create ad
 *     groups and product groups in existing Shopping Campaigns. See
 *     https://developers.google.com/google-ads/scripts/docs/solutions/bulk-shopping-ad-group-creator
 *     for more details.
 *
 * @author Google Ads Scripts Team [adwords-scripts@googlegroups.com]
 *
 * @version 2.0
 *
 * @changelog
 * - version 2.0
 *   - Updated to use new Google Ads scripts features.
 * - version 1.0.2
 *   - Removed use of ProductAdBuilder.withPromotionLine as it is deprecated.
 * - version 1.0.1
 *   - Added validation for external spreadsheet setup.
 * - version 1.0
 *   - Released initial version.
 */

/**
 * SPREADSHEET_URL: URL for spreadsheet to read
 * SHEET_NAME: Name of sheet in spreadsheet to read
 */
const SPREADSHEET_URL = 'YOUR_SPREADSHEET_URL';
const SHEET_NAME = 'YOUR_SHEET_NAME';

/**
 * Column header specification
 * These are the actual text the script looks for in the spreadsheet header.
 */
const CAMPAIGN_COLUMN = 'Campaign';
const AD_GROUP_COLUMN = 'Ad Group';
const MAX_CPC_COLUMN = 'Max CPC';

const PRODUCT_GROUP_COLUMN = 'Product Group';
const AD_GROUP_STATUS_COLUMN = 'AdGroup Status';

const REQUIRED_COLUMN_NAMES = {};
REQUIRED_COLUMN_NAMES[CAMPAIGN_COLUMN] = true;
REQUIRED_COLUMN_NAMES[AD_GROUP_COLUMN] = true;
REQUIRED_COLUMN_NAMES[MAX_CPC_COLUMN] = true;
REQUIRED_COLUMN_NAMES[PRODUCT_GROUP_COLUMN] = true;
REQUIRED_COLUMN_NAMES[AD_GROUP_STATUS_COLUMN] = true;
/** End of column header specification */

/**
 * Reads campaign and bid data from spreadsheet and writes it to Google Ads.
 */
function main() {
  console.log(`Using spreadsheet - ${SPREADSHEET_URL}.`);
  const sheet = validateAndGetSpreadsheet(SPREADSHEET_URL, SHEET_NAME);
  parseSheetAndConstructProductGroups(sheet);

  console.log('Parsed spreadsheet and completed shopping campaign ' +
      'construction.');
}

//Stores results of each row, along with formatting details
let resultsByRow = ['Result', 'Notes'];
let resultColors = ['Black', 'Black'];

/**
 * Validates header of sheet to make sure that header matches expected format.
 * Throws exception if problems are found. Saves the column number of each
 * expected column to global variable.
 *
 * @param {!Array.<string>} headerRow A list of column header names.
 * @return {!Object.<number>} A mapping from column name to column index.
 * @throws If required column is missing.
 */
function validateHeader(headerRow) {
  const result = {};

  const missingColumns = Object.keys(REQUIRED_COLUMN_NAMES);

  // Grab the column # for each expected input column.
  for (let columnIndex = 0; columnIndex < headerRow.length; columnIndex++) {
    const columnName = headerRow[columnIndex];
    if (columnName in REQUIRED_COLUMN_NAMES) {
      result[columnName] = columnIndex;
      const index = missingColumns.indexOf(columnName);
      if (index >= 0) {
        missingColumns.splice(index, 1);
      }
    }
  }

  if (missingColumns.length > 0) {
    throw `Bid sheet data format doesn't match expected format. ` +
        `Missing columns: ${missingColumns.join()}`;
  }
  return result;
}

/**
 * Converts a spreadsheet row into map representation.
 *
 * @param {!Array.<!Object>} row The spreadsheet row.
 * @param {!Object.<number>} headers Mapping from column name to column index.
 * @return {?Object} The row in object form, or null for a parsing error.
 */
function parseRow(row, headers) {
  const parsedRow = {};

  for (const header in headers) {
    const colNum = headers[header];
    let val = row[colNum].toString().trim();
    if (!val) {
      continue;
    }
    // Google Ads Editor will add double quotes (") around string if it contains
    // commas or double quotes, so we need to strip those out.
    if (val.charAt(0) === '"' && val.charAt(val.length - 1) === '"') {
      val = val.substring(1, val.length - 1);
    }
    // Google Ads Editor escapes double quotes (") with another double quote ("").
    val = val.replace(/""/g, '"');
    if (header === PRODUCT_GROUP_COLUMN) {
      const productGroups = [];
      const parsedProductGroups =
          parseEditorFormat(val);
      if (parsedProductGroups.error) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] = parsedProductGroups.error;
        resultColors[0] = 'Red';
        return null;
      }

      for (let x = 0; x < parsedProductGroups.length; x++) {
        // Product group type and value indices are level-1
        // (e.g. for L1, x=0).
        productGroups[x] = {
          type: parsedProductGroups[x][0],
          value: parsedProductGroups[x][1]
        };
      }
      parsedRow[header] = productGroups;
    } else {
      parsedRow[header] = val;
    }
  }

  // Ignore rows that don't have any useful information.
  const testRow = [];
  for (const k in parsedRow) {
    // Remove campaign, ad group columns and test if the rest is empty.
    if (k === CAMPAIGN_COLUMN || k === AD_GROUP_COLUMN) {
      continue;
    }
    testRow.push(parsedRow[k]);
  }
  if (testRow.toString().replace(/[,]+/g, '') === '') {
    resultsByRow[0] = 'SKIPPED';
    resultsByRow[1] = 'Superfluous row';
    return null;
  }
  return parsedRow;
}


/**
 * Parses spreadsheet and constructs ad groups and product groups in Google Ads.
 *
 * @param {!Sheet} sheet The sheet to parse.
 */
function parseSheetAndConstructProductGroups(sheet) {
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
      .getValues()[0];

  const headers = validateHeader(headerRow);

  const values = sheet.getRange(2, 1, sheet.getLastRow() - 1,
      sheet.getLastColumn()).getValues();

  const campaigns = {};

  const outputColumn = sheet.getLastColumn() + 1;
  const resultHeaderRange = sheet.getRange(1, outputColumn, 1, 2);
  resultHeaderRange.setValues([resultsByRow]);
  resultHeaderRange.setFontColors([resultColors]);
  resultHeaderRange.setFontWeights([['Bold', 'Bold']]);

  // Iterate through rows.
  for (let r = 0; r < values.length; r++) {
    resultsByRow = ['', ''];
    resultColors = ['Black', 'Black'];
    const row = values[r];

    const parsedRow = parseRow(row, headers);

    if (parsedRow) {
      let bid = parsedRow[MAX_CPC_COLUMN];
      if (bid) {
        // For European locale, decimal points are commas.
        bid = bid.toString().toLowerCase().replace(/,/g, '.');
        if (bid != 'excluded' && (isNaN(Number(bid)) || !isFinite(bid))) {
          resultsByRow[0] = 'ERROR';
          resultsByRow[1] = 'Invalid bid';
        }
      }
      const campaignName = parsedRow[CAMPAIGN_COLUMN];
      campaigns[campaignName] =
          fetchCampaignIfNecessary(campaigns, campaignName);
      if (campaigns[campaignName]) {
        const adGroupName = parsedRow[AD_GROUP_COLUMN];
        campaigns[campaignName].createdAdGroups[adGroupName] =
            buildAdGroupIfNecessary(parsedRow, campaigns[campaignName],
                adGroupName, bid);
        if (campaigns[campaignName].createdAdGroups[adGroupName]) {
          if (campaigns[campaignName].createdAdGroups[adGroupName].skip) {
            buildProductGroups(parsedRow,
                campaigns[campaignName].createdAdGroups[adGroupName], bid);
          } else {
            resultsByRow[0] = 'SKIPPED';
            resultsByRow[1] =
                'Ad group already exists with product groups';
          }
        }
      }

      if (!resultsByRow[0]) {
        resultsByRow[0] = 'SUCCESS';
      }
    }
    switch (resultsByRow[0]) {
      case 'ERROR':
        resultColors[0] = 'Red';
        break;
      case 'SUCCESS':
        resultColors[0] = 'Green';
        break;
      case 'WARNING':
        resultColors[0] = 'Yellow';
    }
    const resultRange = sheet.getRange(r + 2, outputColumn, 1, 2);
    resultRange.setValues([resultsByRow]);
    resultRange.setFontColors([resultColors]);
    resultRange.setFontWeights([['Bold', 'Normal']]);
  }
}


/**
 * Fetches campaign from Google Ads if it hasn't already been done.
 *
 * @param {!Object.<ShoppingCampaign>} campaigns A cache of campaigns.
 * @param {string} campaignName The name of the campaign to fetch.
 * @return {?ShoppingCampaign} The campaign, or null if not found.
 */
function fetchCampaignIfNecessary(campaigns, campaignName) {
  //Find the campaign
  if (!campaignName) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Missing campaign name';
    return null;
  }
  let campaign = campaigns[campaignName];
  if (!campaign) {
    campaign = findCampaignByName(campaignName);
    campaign.createdAdGroups = {};
    if (!campaign) {
      resultsByRow[0] = 'ERROR';
      resultsByRow[1] = 'Could not find campaign';
    }
  }
  return campaign;
}

/**
 * Builds ad group if necessary, otherwise returns existing ad group.
 *
 * @param {!Array.<Object>} row A spreadsheet row.
 * @param {!ShoppingCampaign} campaign
 * @param {string} adGroupName The ad group to build or fetch.
 * @param {number} bid
 * @return {?ShoppingAdGroup} The ad group of null if there is an error.
 */
function buildAdGroupIfNecessary(row, campaign, adGroupName, bid) {
  if (!adGroupName) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Missing ad group name';
    return null;
  }
  // See if we already fetched/created the ad group.
  let adGroup = campaign.createdAdGroups[adGroupName];
  if (!adGroup) {
    // Only use the bid on this line for the ad group default bid if there are
    // no product groups specified for it. Ad group default bid must be
    // specified.
    if (row[PRODUCT_GROUP_COLUMN]) {
      resultsByRow[0] = 'ERROR';
      resultsByRow[1] = 'Ad Group is missing a default bid.';
      return null;
    }
    // Set default status to enabled.
    let status = 'ENABLED';
    // If ad group status is specified, make sure it's "active", "enabled", or
    // "paused", and set status. Ad group status must be set on the first row
    // that the ad group appears in.
    if (row[AD_GROUP_STATUS_COLUMN]) {
      status = row[AD_GROUP_STATUS_COLUMN].toUpperCase();
      if (status === 'ACTIVE') {
        status = 'ENABLED';
      }
    }
    adGroup = createAdGroup(adGroupName, status, bid, campaign);
    if (adGroup) {
      adGroup.rootProductGroup = adGroup.rootProductGroup();
      adGroup.rootProductGroup.childMap = {};
    }
  }
  return adGroup;
}

/**
 * Builds product groups from row.
 *
 * @param {!Array.<Object>} row A spreadsheet row.
 * @param {!ShoppingAdGroup} adGroup The ad group to operate on.
 * @param {number} bid The product group bid.
 */
function buildProductGroups(row, adGroup, bid) {
  if (!row[PRODUCT_GROUP_COLUMN]) {
    return;
  }
  // Iterate through product groups in row.
  let maxLevel = -1;
  let productGroupsToAdd = row[PRODUCT_GROUP_COLUMN];
  let productGroup = adGroup.rootProductGroup;
  for (let i = 0; i < productGroupsToAdd.length; i++) {
    const type =
        productGroupsToAdd[i].type.toString().toLowerCase()
        .replace(/[ ]+/g, '');
    let val = productGroupsToAdd[i].value.toString().trim();
    if (type) {
      //For each Group level n, row must contain values for 1...n-1
      if (i - maxLevel > 1) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] = 'Every level of the product ' +
            'group type must have all higher ' +
            'level values. L' + i + ' is filled but missing L' +
            (maxLevel + 1);
        return;
      }
      maxLevel = i;

      // Each row must have matching # of bidding attribute type and value.
      if (!val) {
        resultsByRow[0] = 'ERROR';
        resultsByRow[1] =
            'Every product group type must have an associated value. L' +
            i + ' has a type but no value';
        return;
      }

      // Build product groups.
      if (!productGroup.childMap[val.toLowerCase()]) {
        if (val === '*') {
          if (Object.keys(productGroup.childMap).length === 0) {
            resultsByRow[0] = 'ERROR';
            resultsByRow[1] =
                '"Everything else" product group must come after all others';
            return;
          } else {
            let child = productGroup.childMap[val];
            if (!child) {
              const children = productGroup.children().get();
              for (const element of children) {
                child = element;
                if (child.isOtherCase()) {
                  break;
                }
              }
              child.childMap = {};
            }
            productGroup.childMap[val] = child;
            productGroup = child;
            productGroup.setMaxCpc(Number(adGroup.bidding().getCpc()));
            //Only assign the bid to the lowest level product group
            //on that row
            if (i + 1 === productGroupsToAdd.length) {
              if (bid != 'excluded') {
                productGroup.setMaxCpc(Number(bid));
              } else {
                productGroup.exclude();
              }
            }
          }
        } else {
          let productGroupBuilder = productGroup.newChild();
          // Verify that bidding attribute type is valid, construct
          // productGroupBuilder.
          switch (type) {
            case 'producttype':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.productTypeBuilder()
                                                       .withValue(val);
              break;
            case 'brand':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.brandBuilder()
                                                       .withName(val);
              break;
            case 'category':
              productGroupBuilder = productGroupBuilder.categoryBuilder()
                                                       .withName(val);
              break;
            case 'condition':
              val = val.toUpperCase();
              productGroupBuilder = productGroupBuilder.conditionBuilder()
                                                       .withCondition(val);
              break;
            case 'itemid':
              val = val.toLowerCase();
              productGroupBuilder = productGroupBuilder.itemIdBuilder()
                                                       .withValue(val);
              break;
            default:
              if (type.match(/^custom((\\s)?(label|attribute))?/)) {
                val = val.toLowerCase();
                //make sure there's a number at the end that's between 0-4
                if (type.match(/[0-4]$/)) {
                  productGroupBuilder =
                      productGroupBuilder.customLabelBuilder()
                                         .withType('INDEX' +
                                            type.substring(type.length - 1))
                                         .withValue(val);
                } else {
                  resultsByRow[0] = 'ERROR';
                  resultsByRow[1] =
                      'Invalid custom attribute type: ' +
                      productGroupsToAdd[i].type;
                  return;
                }
              } else {
                resultsByRow[0] = 'ERROR';
                resultsByRow[1] =
                    'Invalid bidding attribute type: ' +
                    productGroupsToAdd[i].type;
                return;
              }
          }

          const productGroupOp = productGroupBuilder.build();

          if (!productGroupOp.isSuccessful()) {
            resultsByRow[0] = 'ERROR';
            resultsByRow[1] = 'Error creating product group ' +
                'level ' + (i + 1) + ': ' + productGroupOp.getErrors();
            return;
          }

          const result = productGroupOp.getResult();
          // Only assign the bid to the lowest level product group on that row.
          if (i + 1 === productGroupsToAdd.length) {
            if (bid === 'excluded') {
              result.exclude();
            } else if (bid) {
              result.setMaxCpc(Number(bid));
            }
          }

          result.childMap = {};
          productGroup.childMap[val.toLowerCase()] = result;

          // Set current product group to the newly created product group.
          productGroup = result;
        }
      } else {
        // Set current product group to the last read product group.
        productGroup = productGroup.childMap[val.toLowerCase()];
      }
    }
  }
}

/**
 * Parses Google Ads Editor product group format
 * (e.g. * / Condition='New' / Custom label 2='furniture' /
 *   Product type='bar carts').
 *
 * @param {string} productGroupPath The product group path.
 * @return {!Array.<!Array.<string>>} A list of product group component name/
 *     value pairs.
 */
function parseEditorFormat(productGroupPath) {
  // Ignore * / case which is the root product group.
  if (productGroupPath === '* /' || !productGroupPath) {
    return [];
  }

  const regexVals = productGroupPath.match(new RegExp(/'(.*?)'/g));

  if (regexVals) {
    for (let i = 0; i < regexVals.length; i++) {
      productGroupPath = productGroupPath.replace(regexVals[i], '$' + i);
    }
  }

  const result = [];
  const productGroup = productGroupPath.split('/');

  // Google Ads Editor format starts with '* /' so we ignore first one.
  for (let x = 1; x < productGroup.length; x++) {
    if (!productGroup[x]) {
      continue;
    }
    // Google Ads Editor format looks like: Brand='nike'.
    const pair = productGroup[x].trim().split('=');
    if (pair.length != 2) {
      return {error: 'Product group string malformed. Should have 1 "=", ' +
            'but has ' + (pair.length - 1)};
    }
    let val = pair[1];
    if (val.charAt(0) != '$' && val.charAt(0) != '*') {
      return {error: 'Product group string malformed. Please ensure you are ' +
            'using Google Ads Editor format'};
    }
    // '*' value doesn't have single quotes around it.
    if (val != '*') {
      const values = pair[1].split('$');
      // Skip 0 because it's always blank. String always starts with $.
      for (let i = 1; i < values.length; i++) {
        val = val.replace('$' + values[i], regexVals[values[i]]);
      }
      val = val.substring(1, val.length - 1).replace(/''/g, '\'');
    }

    result.push([pair[0], val]);
  }
  return result;
}

/**
 * Fetches campaign from Google Ads by name.
 *
 * @param {string} name The campaign name.
 * @return {?ShoppingCampaign} The found campaign, or null if not found.
 */
function findCampaignByName(name) {
  const campaignName = name.replace(/'/g, '\\\'');
  const shoppingCampaignSelector = AdsApp
        .shoppingCampaigns()
        .withCondition('Name = \'' + campaignName + '\'');

  let campaign = null;

  const shoppingCampaignIterator = shoppingCampaignSelector.get();
  if (shoppingCampaignIterator.hasNext()) {
    campaign = shoppingCampaignIterator.next();
  }

  return campaign;
}

/**
 * Fetches ad group from Google Ads given ad group name and campaign.
 *
 * @param {string} agName The name of the ad group.
 * @param {!ShoppingCampaign} campaign The campaign within which to search.
 * @return {?ShoppingAdGroup} The ad group or null if not found.
 */
function findAdGroupByName(agName, campaign) {
  const adGroupName = agName.replace(/'/g, '\\\'');
  const adGroupSelector = campaign
        .adGroups()
        .withCondition('Name = \'' + adGroupName + '\'');

  let adGroup = null;

  const adGroupIterator = adGroupSelector.get();
  if (adGroupIterator.hasNext()) {
    adGroup = adGroupIterator.next();
  }

  return adGroup;
}

/**
 * Creates ad group in Google Ads if it doesn't already exist, along with ad
 * group ad.
 *
 * @param {string} name The name of the ad group.
 * @param {string} status The desired status of the ad group.
 * @param {number} defaultBid The max CPC for the ad group.
 * @param {!ShoppingCampaign} campaign The campaign to create the ad group for.
 * @return {?ShoppingAdGroup} The created ad group or null if there is an error.
 */
function createAdGroup(name, status, defaultBid, campaign) {
  // See if ad group exists. If so, fetch it.
  const adGroup = findAdGroupByName(name, campaign);
  if (adGroup != null) {
    if (adGroup.rootProductGroup()) {
      // If root product group exists and not delete, then skip ad group.
      adGroup.skip = true;
    } else {
      adGroup.createRootProductGroup();
    }
    return adGroup;
  }

  // Build ad group.
  const adGroupOp = campaign.newAdGroupBuilder()
        .withName(name)
        .withStatus(status)
        .withMaxCpc(defaultBid)
        .build();

  // Check for errors.
  if (!adGroupOp.isSuccessful()) {
    resultsByRow[0] = 'ERROR';
    resultsByRow[1] = 'Error creating ad group: ' +
        adGroupOp.getErrors();
    return null;
  }

  const adGroupResult = adGroupOp.getResult();

  adGroupResult.createRootProductGroup();

  console.log(`Successfully created ad group [${adGroupResult.getName()}]`);

  // Build ad group ad.
  const adGroupAdOp = adGroupResult.newAdBuilder().build();

  // Check for errors.
  if (!adGroupAdOp.isSuccessful()) {
    resultsByRow[0] = 'WARNING';
    resultsByRow[1] = 'Error creating ad group ad: ' +
        adGroupAdOp.getErrors();
  }

  return adGroupResult;
}

/**
 * DO NOT EDIT ANYTHING BELOW THIS LINE.
 * Please modify your spreadsheet URL and email addresses at the top of the file
 * only.
 */

/**
 * Validates the provided spreadsheet URL and email address
 * to make sure that they're set up properly. Throws a descriptive error message
 * if validation fails.
 *
 * @param {string} spreadsheeturl The URL of the spreadsheet to open.
 * @param {string} sheetname The name of the sheet within the spreadsheet that
 *     should be fetched.
 * @return {Spreadsheet} The spreadsheet object itself, fetched from the URL.
 * @throws {Error} If the spreadsheet URL or email hasn't been set
 */
function validateAndGetSpreadsheet(spreadsheeturl, sheetname) {
  if (spreadsheeturl === 'YOUR_SPREADSHEET_URL') {
    throw new Error('Please specify a valid Spreadsheet URL. You can find' +
        ' a link to a template in the associated guide for this script.');
  }
  if (sheetname === 'YOUR_SHEET_NAME') {
    throw new Error('Please specify the name of the sheet you want to use on' +
        ' your spreadsheet.');
  }
  const spreadsheet = SpreadsheetApp.openByUrl(spreadsheeturl);
  const sheet = spreadsheet.getSheetByName(sheetname);
  return sheet;
}