The code requires various modules, defines constants and functions, and exports an asynchronous function getSheet which retrieves or creates a new sheet based on its link and domain, and adds or updates the sheet's data in a Google Sheets document. The getSheet function uses other functions such as addSheet, updateRow, getInfo, and isInvalidDomain to perform its operations.

Run example

npm run import -- "get sheet purchases"

get sheet purchases

var util = require('util');
var uuid = require('uuid/v1');
var importer = require('../Core');
var getDataSheet = importer.import("google sheet array objects");
var getInfo = importer.import("get google sheet info");
var addRow = importer.import("add row data google sheet");
var updateRow = importer.import("update a row in google sheets")

var purchaseId = '1kWjkjLGxQyzFUzRLBk3LpcjPW3UjcaF-PBMDX_3hZfM';
var project = 'spahaha-ea443';

var isInvalidDomain = (match, domain) =>
    !match || domain && domain !== match.domain && domain !== match.bucket

function safeName(name) {
    return name.replace(/[^a-z0-9\-]/ig, '-').substr(0, 40).toLowerCase();
}

async function addSheet(docId, title, email) {
    var name = safeName(title.replace(/\s*(configuration|config)\s*/ig, ''))
        + '-' + uuid().substr(0, 5);
    return await addRow(purchaseId, {
        timestamp: Date.now(),
        name: title,
        email: email || '',
        address: '',
        domain: '',
        bucket: name + '.sheet-to-web.com',
        project: project,
        sheet: docId
    }, 'Purchases').then(() => name + '.sheet-to-web.com')
}

async function getSheet(link, domain, email) {
    var title, docId;
    var info = await getInfo(link)
    title = info.properties.title;
    docId = info.spreadsheetId;
    var purchases = (await getDataSheet(purchaseId, 'Purchases'))
    var match = purchases.filter(p => p.sheet == docId)[0]
    if(domain && isInvalidDomain(match, domain)) {
        throw new Error(`sheet ${docId} doesn't match domain ${domain}`)
    } else if(!match) {
        return await addSheet(docId, title, email);
    }
    console.log(`Purchase ${docId} already exists: ${match.domain} or ${match.bucket}`);
    return await updateRow(purchaseId, r => r.sheet == docId, Object.assign(match, {
        name: title,
        email
    }), 'Purchases');
}

module.exports = getSheet;

const { google } = require('googleapis');
const { v4: uuidv4 } = require('uuid');
const { importFunction } = require('../Core');

const getDataSheet = importFunction('google sheet array objects');
const getInfo = importFunction('get google sheet info');
const addRow = importFunction('add row data google sheet');
const updateRow = importFunction('update a row in google sheets');

const safeName = (name) => {
  // Replace non-alphanumeric characters and whitespace with hyphens
  const safeName = name.replace(/[^a-z0-9\-]/ig, '-');
  // Trim to 40 characters to prevent excessive length
  return safeName.substr(0, 40).toLowerCase();
};

async function getSheet(auth, link, domain, email) {
  // Extract spreadsheet ID from link
  const docId = link.split('sheet/to/web.com/')[1];

  try {
    // Get spreadsheet info
    const info = await getInfo(auth, link);
    const title = info.data.properties.title;
    const purchases = await getDataSheet(auth, purchaseId, 'Purchases');
    const match = purchases.find((p) => p.sheet === docId);

    if (domain &&!match || isInvalidDomain(match, domain)) {
      // If domain doesn't match, add new sheet
      const name = safeName(title.replace(/\s*(configuration|config)\s*/ig, ''))
        + '-' + uuidv4().substr(0, 5);
      await addRow(auth, purchaseId, {
        timestamp: Date.now(),
        name: title,
        email: email || '',
        address: '',
        domain: '',
        bucket: name + '.sheet-to-web.com',
        project: project,
        sheet: docId,
      }, 'Purchases');
      return name + '.sheet-to-web.com';
    } else if (!match) {
      // If sheet doesn't exist, add new sheet
      return await addSheet(auth, docId, title, email);
    } else {
      // Update existing sheet
      const updatedMatch = Object.assign(match, {
        name: title,
        email,
      });
      await updateRow(auth, purchaseId, r => r.sheet === docId, updatedMatch, 'Purchases');
      console.log(`Purchase ${docId} updated`);
      return updatedMatch.bucket || updatedMatch.domain;
    }
  } catch (error) {
    throw new Error(`Failed to retrieve sheet: ${error.message}`);
  }
}

async function addSheet(auth, docId, title, email) {
  const name = safeName(title.replace(/\s*(configuration|config)\s*/ig, ''))
    + '-' + uuidv4().substr(0, 5);
  await addRow(auth, purchaseId, {
    timestamp: Date.now(),
    name: title,
    email: email || '',
    address: '',
    domain: '',
    bucket: name + '.sheet-to-web.com',
    project: project,
    sheet: docId,
  }, 'Purchases');
  return name + '.sheet-to-web.com';
}

// Usage example:
async function main() {
  const auth = new google.auth.GoogleAuth();
  const link = 'https://sheet-to-web.com/your-spreadsheet-id';
  const domain = 'your-domain.com';
  const email = 'your-email@example.com';
  const result = await getSheet(auth, link, domain, email);
  console.log(result);
}

main();

Code Breakdown

Requires and Imports

The code starts by requiring several modules:

• util: a built-in Node.js module providing utility functions.
• uuid/v1: a module providing a unique identifier generator.
• ../Core: a custom module containing various functions, which are imported as follows:
  • google sheet array objects
  • get google sheet info
  • add row data google sheet
  • update a row in google sheets

Constants and Functions

• purchaseId: a constant identifying a Google Sheets document.
• project: a constant identifying a Google Cloud project.
• isInvalidDomain: a function checking if a domain is invalid.
• safeName: a function generating a safe name by replacing non-alphanumeric characters and trimming excess characters.

Functions

addSheet

• async function: creates a new row in the Purchases sheet.
• arguments:
  • docId: the ID of the Google Sheets document.
  • title: the title of the sheet.
  • email: the email associated with the sheet.
• operations:
  1. Generates a unique name using safeName and uuid.
  2. Adds a new row to the Purchases sheet with the provided data.
  3. Returns the generated name in the format name.sheet-to-web.com.

getSheet

• async function: retrieves or creates a new sheet based on its link and domain.
• arguments:
  • link: the link to the Google Sheets document.
  • domain: the domain to be associated with the sheet.
  • email: the email associated with the sheet.
• operations:
  1. Retrieves information about the sheet using getInfo.
  2. Checks if the sheet already exists in the Purchases sheet.
  3. If the sheet exists:
     a. Checks if the domain is valid using isInvalidDomain.
     b. If the domain is invalid, throws an error.
     c. If the domain is valid, updates the sheet's data using updateRow.
  4. If the sheet does not exist, creates a new sheet using addSheet.

Exports

The getSheet function is exported as a module.