How to Import PayPal Transactions into Google Sheets

This tutorial will show you how to import PayPal transactions into Google Sheets with the help of Google Apps Script. You can choose to import standard PayPal payments, recurring subscription payments, donations, or even refunds and chargebacks into Google Sheets.

Once the data has been imported into Google Sheets, you can export them into a CSV file and import them into Quickbooks accounting software. Tally users in India can export PayPal transactions from Google Sheets into XML format and bulk import them into Tally.

Also see: Automate PayPal with Google Forms

Import PayPal Transactions in Google Sheets

For this example, we will be importing the list of donors into Google Sheets who have made the donations through PayPal.

1. Create API credentials inside PayPal

Sign-in to your PayPal developer dashboard (developer.paypal.com) and create a new app in the live mode. Give your App a name - Transaction Importer for Google Sheets and click the Create App button.

PayPal will create a Client ID and Client Secret key that you will need in a later step. Under the Live App settings section, check the Transaction Search option and turn off all other options since we only want the API keys to list transactions and have no other functionality. Click Save to continue.

2. Create a Google Sheets Project

Go to sheets.new to create a new Google Sheet. Go to Extensions menu and choose Apps Script to open the Apps Script editor.

Copy-paste the code in the editor. Remember to replace the transaction code with your own. You can use T0002 for PayPal Subscriptions, T0014 for Donation payments, or T1107 for PayPal Refunds and chargebacks.

The /* @OnlyCurrentDoc */ comment is a Google Apps Script comment that tells Google Apps Script to only run the code inside the current Google Sheet and not require access to any another spreadsheet in your Google Drive.

/* @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);
  }
};

3. Run PayPal Import Function

Inside the script editor, click the Run button to import transactions from PayPal. You may have to authorize the script since it requires permissions to connect to the PayPal API and also write data to Google Sheets on your behalf.

That’s it. If there are any PayPal transactions to import in the selected date range, the script will run and the transactions will be imported into Google Sheets.

In the next part of the tutorial, we will learn how to export the PayPal transactions from Google Sheets to an XML file for importing into Tally accounting software.

Also see: Send PayPal Invoices from Google Sheets

from Digital Inspiration https://ift.tt/T3vMD4I

How to Get the Last Row in Google Sheets when using ArrayFormula

Here we have an employee list spreadsheet with a column named Employee Name and a column named Employee ID.

As soon as you enter a new employee name in the Employee Name column, the Employee ID column will automatically be filled with the help of an ARRAY FORMULA provided below:

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

The formula adds the current row number to the Employee ID column if the current row is not the first row. If the current row is the first row, then the title is added to the cell.

Also see: Google Sheets Formulas for Google Forms

The system works but there’s one major flaw in this approach. Let me explain:

Say you have several new employees and you would like to add them to the spreadsheet programmatically with the help of Google Apps Script.

You’ll get the reference of the sheet and then use the the getLastRow() method to find the last row number to return the first empty row that does not contain any data.

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

The above code will return 10 and not 4 as you would have expected. The reason is that the ArrayFormula affects the getLastRow() method since it outputs an array of blank values all the way to the bottom of the sheet.

Thus the output of getLastRow() and getMaxRows() would be the same if the ArrayFormula is not constrained to size of range that contains actual data.

The fix is surprisingly simple. If the condition in ArrayFormula is not met, leave the second argument blank as show below. The last comma is required though else it will output the default value of FALSE.

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

Here’s the final working code:

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]));
}

You may use this approach to add unique IDs to your Google Sheets.

If it is difficult for you to rewrite the formulas in your Google Sheet, alternate approach would be to get all the data in the sheet and look for the last row that contains data.

We reverse the array to look from blank rows from the bottom and stop as soon as any row containing data is found.

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;
}

from Digital Inspiration https://ift.tt/S5qtWiR

Find and Remove Inactive Users in your Google Workspace Domain

You can use Google Apps Script to find all the inactive user accounts in your Google Workspace domain. The script will find all the users that have not logged in to the domain for a period of time (say 6 months). You also have the option to delete the dormant accounts from Workspace domain and save on your monthly bills.

Find the inactive users in Google Workspace domain

We can use the Admin Directory service of Apps Script to list all the users (active and inactive) in a Google Workspace domain. Open a new script, go to Service section and enable the Admin Directory service.

Next, go to the Google Cloud project associated with your Apps Script project. Switch to the Library section, search for Admin SDK and enable the API. The required OAuth scope is https://www.googleapis.com/auth/admin.directory.user and it should be listed in your appsscript.json file.

{
  "timeZone": "Asia/Kolkata",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "AdminDirectory",
        "version": "directory_v1",
        "serviceId": "admin"
      }
    ]
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/admin.directory.user"],
  "runtimeVersion": "V8"
}

The script will list all users in the domain and find the dormant accounts based on the last login date. If a user has not logged into his or her account in the last, say, 6 months, then the user is considered to be inactive and may be removed.

const getInactiveAccounts = () => {
  let accounts = [];
  let pageToken = null;

  // Replace example.com with your domain name.
  do {
    const { users, nextPageToken = null } = AdminDirectory.Users.list({
      domain: 'example.com',
      customer: 'my_customer',
      maxResults: 100,
      orderBy: 'email',
      pageToken,
    });

    pageToken = nextPageToken;
    accounts = [...accounts, ...users];
  } while (pageToken !== null);

  // delete users who haven't logged in the last 6 months
  const MONTHS = 6;
  const cutOffDate = new Date();
  cutOffDate.setMonth(cutOffDate.getMonth() - MONTHS);

  const inactiveAccounts = accounts
    .filter(({ isAdmin }) => isAdmin === false) // Skip users with admin priveleges
    .filter(({ lastLoginTime }) => {
      const lastLoginDate = new Date(lastLoginTime);
      return lastLoginDate < cutOffDate;
    })
    .const(({ primaryEmail }) => primaryEmail); // Get only the email address

  Logger.log(`We found ${inactiveAccounts.length} inactive accounts in the domain.`);
  Logger.log(`The list is: ${inactiveAccounts.join(', ')}`);

  // Set this to true if you really want to delete the inactive accounts
  const DELETE_USER = false;

  if (DELETE_USER) {
    // Remove the users from the domain
    inactiveAccounts.forEach((userEmail) => {
      AdminDirectory.Users.remove(userEmail);
      Logger.log(`Deleted Google Workspace account for ${userEmail}`);
    });
  }
};

from Digital Inspiration https://ift.tt/64MH8ec

How to Extract URLs from HYPERLINK Function in Google Sheets

The HYPERLINK formula of Google Sheets lets you insert hyperlinks into your spreadsheets. The function takes two arguments:

1. The full URL of the link
2. The description or the anchor text of the link

The URL and anchor text can either be specified as a string or as a cell reference.

If you insert a hyperlink into a cell using the HYPERLINK function, there’s no direct way to extract the URL from the formula. You may consider writing a complicated Regular Expression to match and extract the hyperlink in the cell formula or use Apps Script with Google Sheets API.

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);
};

Also see: Replace Text in Google Docs with RegEx

from Digital Inspiration https://ift.tt/iYCKVAa