Tag: Google Sheets

=$C:$C=max($C:$C)

=AND(C2>5000, B2="CA")

=DAYS(TODAY(),$B:$B)>=30

=AND(YEAR(TODAY())-YEAR($B2)>=16,MONTH($B2)=MONTH(TODAY()))

=REGEXMATCH(UPPER($B:$B), "^(CA|NY|FL)$")

=MATCH($B1, INDIRECT("'List of States'!A1:A"),0)

const applyConditionalFormatting = () => {
  const sheet = SpreadsheetApp.getActiveSheet();

  const color = SpreadsheetApp.newColor().setThemeColor(SpreadsheetApp.ThemeColorType.BACKGROUND).build();

  const rule1 = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([sheet.getRange('B:B')])
    .whenTextEqualTo('CA')
    .setUnderline(true)
    .setBold(true)
    .setBackground(color)
    .build();

  const rule2 = SpreadsheetApp.newConditionalFormatRule()
    .setRanges([sheet.getRange('A1:C15')])
    .whenFormulaSatisfied('=$C1>5000')
    .setBackground('green')
    .setFontColor('#00FF00')
    .build();

  const conditionalFormatRules = sheet.getConditionalFormatRules();
  conditionalFormatRules.push(rule1);
  conditionalFormatRules.push(rule2);

  sheet.setConditionalFormatRules(conditionalFormatRules);
};

/* @OnlyCurrentDoc */
/* Author: digitalinspiration.com */

const TRANSACTION_TYPE = 'T0001';

// Enter your own PayPal Client ID and Client Secret key
const PAYPAL_CLIENT_ID = '<YOUR_PAYPAL_CLIENT_ID>';
const PAYPAL_CLIENT_SECRET = '<YOUR_PAYPAL_CLIENT_SECRET>';

// Enter start and end dates in the format YYYY-MM-DD
const START_DATE = '2022-03-01';
const END_DATE = '2022-03-15';

// Generate the PayPal access token
const getPayPalAccessToken_ = () => {
  const credentials = `${PAYPAL_CLIENT_ID}:${PAYPAL_CLIENT_SECRET}`;
  const headers = {
    Authorization: ` Basic ${Utilities.base64Encode(credentials)}`,
    Accept: 'application/json',
    'Content-Type': 'application/json',
    'Accept-Language': 'en_US',
  };

  const options = {
    method: 'POST',
    headers,
    contentType: 'application/x-www-form-urlencoded',
    payload: { grant_type: 'client_credentials' },
  };

  const request = UrlFetchApp.fetch('https://api.paypal.com/v1/oauth2/token', options);
  const { access_token } = JSON.parse(request);

  return access_token;
};

// Append the query parameters to the PayPal API URL
const buildAPIUrl_ = (queryParams) => {
  const baseUrl = [`https://api-m.paypal.com/v1/reporting/transactions`];
  Object.entries(queryParams).forEach(([key, value], index) => {
    const prefix = index === 0 ? '?' : '&';
    baseUrl.push(`${prefix}${key}=${value}`);
  });
  return baseUrl.join('');
};

// Fetch the list of PayPal transaction
const fetchTransactionBatchFromPayPal = (queryParams) => {
  const options = {
    headers: {
      Authorization: `Bearer ${getPayPalAccessToken_()}`,
      'Content-Type': 'application/json',
    },
  };

  const request = UrlFetchApp.fetch(buildAPIUrl_(queryParams), options);
  const { transaction_details, total_pages } = JSON.parse(request);
  return { transaction_details, total_pages };
};

// Extract the transaction details including the transaction ID,
// donation amount, transaction date and buyer's email and country code
const parsePayPalTransaction_ = ({ transaction_info, payer_info }) => [
  transaction_info.transaction_id,
  new Date(transaction_info.transaction_initiation_date),
  transaction_info.transaction_amount?.value,
  transaction_info.transaction_note || transaction_info.transaction_subject || '',
  payer_info?.payer_name?.alternate_full_name,
  payer_info?.email_address,
  payer_info?.country_code,
];

const fetchPayPalTransactions_ = () => {
  const startDate = new Date(START_DATE);
  const endDate = new Date(END_DATE);
  startDate.setHours(0, 0, 0, 0);
  endDate.setHours(23, 59, 59, 999);

  const transactions = [];

  const params = {
    start_date: startDate.toISOString(),
    end_date: endDate.toISOString(),
    page_size: 100,
    transaction_type: TRANSACTION_TYPE,
    fields: 'transaction_info,payer_info',
  };

  for (let page = 1, hasMore = true; hasMore; page += 1) {
    const response = fetchTransactionBatchFromPayPal({ ...params, page });
    const { transaction_details = [], total_pages } = response;
    transaction_details.map(parsePayPalTransaction_).forEach((e) => transactions.push(e));
    hasMore = total_pages && total_pages > page;
  }

  return transactions;
};

// Import the transactions from PayPal and write them to the active Google Sheet
const importTransactionsToGoogleSheet = () => {
  const transactions = fetchPayPalTransactions_();
  const { length } = transactions;
  if (length > 0) {
    const sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(1, 1, length, transactions[0].length).setValues(transactions);
    const status = `Imported ${length} PayPal transactions into Google Sheets`;
    SpreadsheetApp.getActiveSpreadsheet().toast(status);
  }
};

=ARRAYFORMULA(IF(ROW(A:A)=1, "Employee ID",
  IF(NOT(ISBLANK(A:A)), ROW(A:A)-1, "")))

function addNewEmployees() {
  const employees = ['Richard', 'Elizabeth', 'Orli'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  Logger.log('Last row is %s', lastRow);
}

=ARRAYFORMULA(IF(ROW(A:A)=1, "Employee ID",
  IF(NOT(ISBLANK(A:A)), ROW(A:A)-1,)))

function addNewEmployees() {
  const employees = ['Richard', 'Elizabeth', 'Orli'];
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  Logger.log('Last row is %s', lastRow);
  sheet.getRange(lastRow + 1, 1, employees.length, 1).setValues(employees.map((e) => [e]));
}

function getLastRow() {
  const data = SpreadsheetApp.getActiveSheet()
    .getRange('A:A')
    .getValues()
    .reverse()
    .map(([employee]) => employee);

  const { length } = data;
  for (var d = 0; d < length; d++) {
    if (data[d]) {
      Logger.log('The last row is %s', length - d);
      return length - d;
    }
  }
  return 1;
}

const extractHyperlinksInSheet = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SpreadsheetApp.getActiveSheet();

  const hyperlinks = [];

  const spreadsheedId = ss.getId();
  const sheetName = sheet.getName();

  const getRange = (row, col) => {
    const address = sheet.getRange(row + 1, col + 1).getA1Notation();
    return `${sheetName}!${address}`;
  };

  const getHyperlink = (rowIndex, colIndex) => {
    const { sheets } = Sheets.Spreadsheets.get(spreadsheedId, {
      ranges: [getRange(rowIndex, colIndex)],
      fields: 'sheets(data(rowData(values(formattedValue,hyperlink))))',
    });
    const [{ formattedValue, hyperlink }] = sheets[0].data[0].rowData[0].values;
    hyperlinks.push({ rowIndex, colIndex, formattedValue, hyperlink });
  };

  sheet
    .getDataRange()
    .getFormulas()
    .forEach((dataRow, rowIndex) => {
      dataRow.forEach((cellValue, colIndex) => {
        if (/=HYPERLINK/i.test(cellValue)) {
          getHyperlink(rowIndex, colIndex);
        }
      });
    });

  Logger.log(hyperlinks);
};

=SORT(Customers!A2:D50, RANDARRAY(ROWS(Customers!A2:A50), 1), FALSE)

/** @OnlyCurrentDoc */

// Sort data in random order
const sortRowsInRandomOrder = () => {
  // Get the current sheet that contains the list of data
  const sheet = SpreadsheetApp.getActiveSheet();

  // Get the first non-empty column
  const column = sheet.getLastColumn() + 1;

  // Add the RAND() formula to all rows in the new column
  sheet
    .getRange(1, column)
    .setFormula("=RAND()")
    .autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

  // Sort the entire range of data using the random values
  // Do not include the first row of data (header) for sort
  sheet.getDataRange().offset(1, 0).sort({ column });

  // Remove the temporary column from Google sheet
  sheet.deleteColumn(column);

  // Flush the changes
  SpreadsheetApp.flush();
};

// Add the menu to Google Sheets
const onOpen = () => {
  SpreadsheetApp.getUi()
    .createMenu("Randomize Rows")
    .addItem("Start", "sortRowsInRandomOrder")
    .addToUi();
};

const latinRegEx = /[\xc0-\xd6\xd8-\xf6\xf8-\xff\u0100-\u017f]/g;
const comboRegEx = `[\\u0300-\\u036f\\ufe20-\\ufe2f\\u20d0-\\u20ff]`;

/** Used to map Latin Unicode letters to basic Latin letters. */
const latinUnicodeLetters = {
  // Latin-1 Supplement block.
  '\xc0': 'A', '\xc1': 'A', '\xc2': 'A', '\xc3': 'A', '\xc4': 'A', '\xc5': 'A',
  '\xe0': 'a', '\xe1': 'a', '\xe2': 'a', '\xe3': 'a', '\xe4': 'a', '\xe5': 'a',
  '\xc7': 'C', '\xe7': 'c',
  '\xd0': 'D', '\xf0': 'd',
  '\xc8': 'E', '\xc9': 'E', '\xca': 'E', '\xcb': 'E',
  '\xe8': 'e', '\xe9': 'e', '\xea': 'e', '\xeb': 'e',
  '\xcc': 'I', '\xcd': 'I', '\xce': 'I', '\xcf': 'I',
  '\xec': 'i', '\xed': 'i', '\xee': 'i', '\xef': 'i',
  '\xd1': 'N', '\xf1': 'n',
  '\xd2': 'O', '\xd3': 'O', '\xd4': 'O', '\xd5': 'O', '\xd6': 'O', '\xd8': 'O',
  '\xf2': 'o', '\xf3': 'o', '\xf4': 'o', '\xf5': 'o', '\xf6': 'o', '\xf8': 'o',
  '\xd9': 'U', '\xda': 'U', '\xdb': 'U', '\xdc': 'U',
  '\xf9': 'u', '\xfa': 'u', '\xfb': 'u', '\xfc': 'u',
  '\xdd': 'Y', '\xfd': 'y', '\xff': 'y',
  '\xc6': 'Ae', '\xe6': 'ae',
  '\xde': 'Th', '\xfe': 'th',
  '\xdf': 'ss',
  // Latin Extended-A block.
  '\u0100': 'A', '\u0102': 'A', '\u0104': 'A',
  '\u0101': 'a', '\u0103': 'a', '\u0105': 'a',
  '\u0106': 'C', '\u0108': 'C', '\u010a': 'C', '\u010c': 'C',
  '\u0107': 'c', '\u0109': 'c', '\u010b': 'c', '\u010d': 'c',
  '\u010e': 'D', '\u0110': 'D', '\u010f': 'd', '\u0111': 'd',
  '\u0112': 'E', '\u0114': 'E', '\u0116': 'E', '\u0118': 'E', '\u011a': 'E',
  '\u0113': 'e', '\u0115': 'e', '\u0117': 'e', '\u0119': 'e', '\u011b': 'e',
  '\u011c': 'G', '\u011e': 'G', '\u0120': 'G', '\u0122': 'G',
  '\u011d': 'g', '\u011f': 'g', '\u0121': 'g', '\u0123': 'g',
  '\u0124': 'H', '\u0126': 'H', '\u0125': 'h', '\u0127': 'h',
  '\u0128': 'I', '\u012a': 'I', '\u012c': 'I', '\u012e': 'I', '\u0130': 'I',
  '\u0129': 'i', '\u012b': 'i', '\u012d': 'i', '\u012f': 'i', '\u0131': 'i',
  '\u0134': 'J', '\u0135': 'j',
  '\u0136': 'K', '\u0137': 'k', '\u0138': 'k',
  '\u0139': 'L', '\u013b': 'L', '\u013d': 'L', '\u013f': 'L', '\u0141': 'L',
  '\u013a': 'l', '\u013c': 'l', '\u013e': 'l', '\u0140': 'l', '\u0142': 'l',
  '\u0143': 'N', '\u0145': 'N', '\u0147': 'N', '\u014a': 'N',
  '\u0144': 'n', '\u0146': 'n', '\u0148': 'n', '\u014b': 'n',
  '\u014c': 'O', '\u014e': 'O', '\u0150': 'O',
  '\u014d': 'o', '\u014f': 'o', '\u0151': 'o',
  '\u0154': 'R', '\u0156': 'R', '\u0158': 'R',
  '\u0155': 'r', '\u0157': 'r', '\u0159': 'r',
  '\u015a': 'S', '\u015c': 'S', '\u015e': 'S', '\u0160': 'S',
  '\u015b': 's', '\u015d': 's', '\u015f': 's', '\u0161': 's',
  '\u0162': 'T', '\u0164': 'T', '\u0166': 'T',
  '\u0163': 't', '\u0165': 't', '\u0167': 't',
  '\u0168': 'U', '\u016a': 'U', '\u016c': 'U', '\u016e': 'U', '\u0170': 'U', '\u0172': 'U',
  '\u0169': 'u', '\u016b': 'u', '\u016d': 'u', '\u016f': 'u', '\u0171': 'u', '\u0173': 'u',
  '\u0174': 'W', '\u0175': 'w',
  '\u0176': 'Y', '\u0177': 'y', '\u0178': 'Y',
  '\u0179': 'Z', '\u017b': 'Z', '\u017d': 'Z',
  '\u017a': 'z', '\u017c': 'z', '\u017e': 'z',
  '\u0132': 'IJ', '\u0133': 'ij',
  '\u0152': 'Oe', '\u0153': 'oe',
  '\u0149': "'n", '\u017f': 's'
};

const basePropertyOf = (object) => (key) => object[key];
const characterMap = basePropertyOf(latinUnicodeLetters);

/**
 * Replace accented characters in Google Sheets with English letters.
 *
 * @param {string} input The input string with accented characters.
 * @return The input without accented characters.
 * @customfunction
 */
function REPLACE_ACCENTED(input) {
  if (input && typeof input === "string") {
    return input.replace(latinRegEx, characterMap).replace(comboRegEx, "");
  }
  return input;
}

const getNotionService = () => {
  return OAuth2.createService("Notion")
    .setAuthorizationBaseUrl("https://api.notion.com/v1/oauth/authorize")
    .setTokenUrl("https://api.notion.com/v1/oauth/token")
    .setClientId(CLIENT_ID)
    .setClientSecret(CLIENT_SECRET)
    .setCallbackFunction("authCallback")
    .setPropertyStore(PropertiesService.getUserProperties())
    .setCache(CacheService.getUserCache())
    .setTokenHeaders({
      Authorization: `Basic ${Utilities.base64Encode(
        `${CLIENT_ID}:${CLIENT_SECRET}`
      )}`,
    });
};

const authCallback = (request) => {
  const isAuthorized = getNotionService().handleCallback(request);
  return HtmlService.createHtmlOutput(
    isAuthorized ? "Success!" : "Access Denied!"
  );
};

const getRedirectUri = () => {
  console.log(OAuth2.getRedirectUri());
};

function getDatabasesList() {
  var service = getNotionService();
  if (service.hasAccess()) {
    const url = "https://api.notion.com/v1/databases";
    const response = UrlFetchApp.fetch(url, {
      headers: {
        Authorization: `Bearer ${service.getAccessToken()}`,
        "Notion-Version": "2021-05-13",
      },
    });
    const { results = [] } = JSON.parse(response.getContentText());
    const databases = results
      .filter(({ object }) => object === "database")
      .map(({ id, title: [{ plain_text: title }] }) => ({ id, title }));
    console.log({ databases });
  } else {
    console.log("Please authorize access to Notion");
    console.log(service.getAuthorizationUrl());
  }
}

/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The distance in miles
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode) => {
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();

  if (!data) {
    throw new Error('No route found!');
  }

  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  return distance;
};

/**
 * Use Reverse Geocoding to get the address of
 * a point location (latitude, longitude) on Google Maps.
 *
 * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
 *
 * @param {String} latitude The latitude to lookup.
 * @param {String} longitude The longitude to lookup.
 * @return {String} The postal address of the point.
 * @customFunction
 */

const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => {
  const { results: [data = {}] = [] } = Maps.newGeocoder().reverseGeocode(latitude, longitude);
  return data.formatted_address;
};

/**
 * Get the latitude and longitude of any
 * address on Google Maps.
 *
 * =GOOGLEMAPS_LATLONG("10 Hanover Square, NY")
 *
 * @param {String} address The address to lookup.
 * @return {String} The latitude and longitude of the address.
 * @customFunction
 */
const GOOGLEMAPS_LATLONG = (address) => {
  const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address);
  if (data === null) {
    throw new Error('Address not found!');
  }
  const { geometry: { location: { lat, lng } } = {} } = data;
  return `${lat}, ${lng}`;
};

/**
 * Find the driving direction between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The driving direction
 * @customFunction
 */
const GOOGLEMAPS_DIRECTIONS = (origin, destination, mode = 'driving') => {
  const { routes = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!routes.length) {
    throw new Error('No route found!');
  }
  return routes
    .map(({ legs }) => {
      return legs.map(({ steps }) => {
        return steps.map((step) => {
          return step.html_instructions.replace(/<[^>]+>/g, '');
        });
      });
    })
    .join(', ');
};

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = 'driving') => {
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error('No route found!');
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  return time;
};

// The cache key for "New York" and "new york  " should be same
const md5 = (key = '') => {
  const code = key.toLowerCase().replace(/\s/g, '');
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
    .map((char) => (char + 256).toString(16).slice(-2))
    .join('');
};

const getCache = (key) => {
  return CacheService.getDocumentCache().get(md5(key));
};

// Store the results for 6 hours
const setCache = (key, value) => {
  const expirationInSeconds = 6 * 60 * 60;
  CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = 'driving') => {
  const key = ['duration', origin, destination, mode].join(',');
  // Is result in the internal cache?
  const value = getCache(key);
  // If yes, serve the cached result
  if (value !== null) return value;
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error('No route found!');
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  // Store the result in internal cache for future
  setCache(key, time);
  return time;
};