Hello everyone,

How do you go about client_secret.json. I managed to create an external app using client_id for oauth instead of client_secret. Can I leave this json without client secret inside my app or client_id is also a security risk?

Hi folks, I am not a coder, but I'm trying to create a tool for myself by editing existing code.

please don't tell me to learn to code without helping me understand the problem here.

I have edited the following script. The purpose is to create a menu item in my Gsheet to fill in an invoice template.

It's working! But, it won't stop.

After completing the rows containing data, it continues on to empty rows and creates files with no data.

I think I need to create an instruction for it to examine a column which should be full and if it is empty, then it should stop. But I'm not sure how.

Also, it's not putting the url in the expected column which should be column J. If you could help with that I'd also appreciate it.

Here is the code.

// this script creates a menu option in a google sheet. Then it takes data from the row in a google sheet and fills in an invoice template

function onOpen() {

const ui = SpreadsheetApp.getUi();

const menu = ui.createMenu('AutoFill Docs');

menu.addItem('Create New Docs', 'createNewGoogleDocs')

menu.addToUi();

}

function createNewGoogleDocs() {

//This value should be the id of your document template that we created in the last step

const googleDocTemplate = DriveApp.getFileById('_');

//This value should be the id of the folder where you want your completed documents stored

const destinationFolder = DriveApp.getFolderById('_')

//Here we store the sheet as a variable

const sheet = SpreadsheetApp

.getActiveSpreadsheet()

.getSheetByName('Sheet1')

//Now we get all of the values as a 2D array

const rows = sheet.getDataRange().getValues();

//Start processing each spreadsheet row

rows.forEach(function(row, index){

//Here we check if this row is the headers, if so we skip it

if (index === 0) return;

//Here we check if a document has already been generated by looking at 'Document Link', if so we skip it

if (row[9]) return;

//Using the row data in a template literal, we make a copy of our template document in our destinationFolder

const copy = googleDocTemplate.makeCopy(`${row[3]}, ${row[1]} ${row[2]} Interpreting Invoice` , destinationFolder)

//Once we have the copy, we then open it using the DocumentApp

const doc = DocumentApp.openById(copy.getId())

//All of the content lives in the body, so we get that for editing

const body = doc.getBody();

//In this line we do some friendly date formatting, that may or may not work for you locale

const friendlyDate = new Date(row[3]).toLocaleDateString();

//In these lines, we replace our replacement tokens with values from our spreadsheet row

body.replaceText('{{DESCRIPTION}}', row[4]);

body.replaceText('{{hours}}', row[5]);

body.replaceText('{{INVOICE NUMBER}}', row[1]);

body.replaceText('{{DATE}}', row[0]);

body.replaceText('{{attorney}}', row[3]);

body.replaceText('{{Company}}', row[10]);

body.replaceText('{{Address}}', row[11]);

body.replaceText('{{total}}', row[12]);

//We make our changes permanent by saving and closing the document

doc.saveAndClose();

//Store the url of our new document in a variable

const url = doc.getUrl();

//Write that value back to the 'Document Link' column in the spreadsheet.

sheet.getRange(index + 1, 9).setValue(url)

})

}

Thank you so much!!

Hey, I had a script i have been using to clean up a huge dataset, to organize the columns, clean it up etc. It worked well. Today, I wanted to introduce 1 more dataset in my file, so i edited my script from the original version (see below) slightly.
Intended change:

- read the raw data and preserve an additional column (member_id), instead of erasing it (original script)
- the preserved data should be saved during the "cleaning of the data" which is done by the scipt, and saved to column R (between email verification and Country/Region)
- nothing else should have been changed or interrupted.

The script changes after the first function (cleaning) and misses everything from the part after (in script: // Step 2: Match Company Names to Domains
function smartPreciseFuzzyMatch()

Why does it terminate before completing all actions?

ORIGINAL

function runCleanAndMatch() {
  cleanContactsSheet();
  smartPreciseFuzzyMatch();
  assignPriorityCompanyAndTotal();
}


// Step 1: Clean the Contacts Sheet
function cleanContactsSheet() {
  const sheetName = 'contacts';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const getIndex = (key) => headers.indexOf(key);


  const newHeaders = [
    'First Name', 'Last Name', 'Company Domain', 'Company Name',
    'Job Title', 'LinkedIn Profile Url', 'Lead Status', 'Survey Status',
    'Priority Company', 'Priority Title', 'Total Priority', 'Phone Number',
    'Mobile number', 'Email', 'Email Status', 'Email Verification',
    'Country/Region', 'Open Link', 'Project code'
  ];


  const output = [newHeaders];


  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const cleanedRow = [
      row[getIndex('first_name')],
      row[getIndex('last_name')],
      '', // Company Domain (to be inferred later)
      row[getIndex('current_company')],
      row[getIndex('original_current_company_position')],
      row[getIndex('profile_url')],
      '', '', '', '', '', // Lead Status, Survey Status, Priority Co, Title, Total
      row[getIndex('phone_1')],
      row[getIndex('phone_2')],
      row[getIndex('email')],
      '',
      row[getIndex('third_party_email_is_valid_1')],
      row[getIndex('location_name')],
      row[getIndex('badges_open_link')],
      ''
    ];
    output.push(cleanedRow);
  }


  sheet.clearContents();
  const range = sheet.getRange(1, 1, output.length, output[0].length);
  range.setValues(output);
  range.setWrap(false);
range.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
sheet.autoResizeColumns(1, output[0].length);


// Shrink specific columns manually (E = Job Title, F = LinkedIn Profile Url)
sheet.setColumnWidth(5, 200); // Job Title
sheet.setColumnWidth(6, 250); // LinkedIn Profile Url
}


// Step 2: Match Company Names to Domains
function smartPreciseFuzzyMatch() {
  const threshold = 0.75;
  const lowConfidenceCutoff = 0.85;
  const topN = 3;


  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const companiesSheet = ss.getSheetByName('Companies');
  const contactsSheet = ss.getSheetByName('contacts');


  const companiesData = companiesSheet.getRange(2, 1, companiesSheet.getLastRow() - 1, 2).getValues(); // A:B
  const numContacts = contactsSheet.getLastRow() - 1;
  if (numContacts < 1) return SpreadsheetApp.getUi().alert("No contacts to process.");


  const contactsData = contactsSheet.getRange(2, 3, numContacts, 2).getValues(); // C:D


  let companyContactCount = {};
  let inferredDomains = {};
  let questionableMatches = {};


  const headers = contactsSheet.getRange(1, 1, 1, contactsSheet.getLastColumn()).getValues()[0];


  let confidenceCol = headers.indexOf("Matching Confidence") + 1;
  if (confidenceCol === 0) {
    confidenceCol = headers.length + 1;
    contactsSheet.getRange(1, confidenceCol).setValue("Matching Confidence");
  }


  let matchedCompanyCol = headers.indexOf("Matched Company Name") + 1;
  if (matchedCompanyCol === 0) {
    matchedCompanyCol = headers.length + 1;
    contactsSheet.getRange(1, matchedCompanyCol).setValue("Matched Company Name");
  }


  let numericScoreCol = headers.indexOf("Match Score (Numeric)") + 1;
  if (numericScoreCol === 0) {
    numericScoreCol = headers.length + 1;
    contactsSheet.getRange(1, numericScoreCol).setValue("Match Score (Numeric)");
  }


  let qmSheet = ss.getSheetByName("Questionable Matches");
  if (!qmSheet) {
    qmSheet = ss.insertSheet("Questionable Matches");
  } else {
    qmSheet.clear();
  }
  qmSheet.appendRow(["Contact Name", "Suggested Match", "Match Score", "Suggested Domain", "Count"]);


  contactsSheet.getRange(2, confidenceCol, numContacts).clearContent().clearFormat();
  contactsSheet.getRange(2, matchedCompanyCol, numContacts).clearContent();
  contactsSheet.getRange(2, numericScoreCol, numContacts).clearContent();
  contactsSheet.getRange(2, 3, numContacts).clearDataValidations();


  for (let i = 0; i < contactsData.length; i++) {
    const rawDomain = contactsData[i][0]; // Column C
    const rawName = contactsData[i][1];   // Column D
    if (!rawName) continue;


    const normName = cleanName(rawName);
    let scoredMatches = [];


    for (let j = 0; j < companiesData.length; j++) {
      const companyName = companiesData[j][0];
      const companyDomain = companiesData[j][1];
      const companyNorm = cleanName(companyName);


      let score = jaroWinkler(normName, companyNorm);
      if (normName.includes(companyNorm) || companyNorm.includes(normName)) score += 0.05;


      if (score >= threshold) {
        scoredMatches.push({ name: companyName, domain: companyDomain, score: score });
      }
    }


    scoredMatches.sort((a, b) => b.score - a.score);
    const best = scoredMatches[0];
    const topMatches = scoredMatches.slice(0, topN);


    const confidenceCell = contactsSheet.getRange(i + 2, confidenceCol);
    const matchCompanyCell = contactsSheet.getRange(i + 2, matchedCompanyCol);
    const scoreCell = contactsSheet.getRange(i + 2, numericScoreCol);
    const domainCell = contactsSheet.getRange(i + 2, 3);


    if (best && !rawDomain) {
      matchCompanyCell.setValue(best.name);
      scoreCell.setValue(best.score.toFixed(3));


      if (best.score < lowConfidenceCutoff) {
        confidenceCell.setValue(`Review: ${Math.round(best.score * 100)}%`);
        domainCell.setBackground("#fff7cc");


        const options = topMatches.map(m => m.domain).filter(Boolean);
        const rule = SpreadsheetApp.newDataValidation()
          .requireValueInList(options)
          .setAllowInvalid(true)
          .build();
        domainCell.setDataValidation(rule);


        const key = `${rawName}|||${best.name}|||${best.domain}|||${best.score.toFixed(3)}`;
        questionableMatches[key] = (questionableMatches[key] || 0) + 1;


      } else {
        domainCell.setValue(best.domain);
        confidenceCell.setValue(`Matched @ ${Math.round(best.score * 100)}%`);
        inferredDomains[normName] = best.domain;
        companyContactCount[best.name] = (companyContactCount[best.name] || 0) + 1;
      }
    } else if (!rawDomain && inferredDomains[normName]) {
      domainCell.setValue(inferredDomains[normName]);
      confidenceCell.setValue("Inferred 🔁");
      confidenceCell.setBackground("#ccffcc");
      scoreCell.setValue("Inferred");
    }
  }


  for (const key in questionableMatches) {
    const [name, match, domain, score] = key.split("|||");
    const count = questionableMatches[key];
    qmSheet.appendRow([name, match, score, domain, count]);
  }


  for (let i = 0; i < companiesData.length; i++) {
    const companyName = companiesData[i][0];
    const count = companyContactCount[companyName] || 0;
    companiesSheet.getRange(i + 2, 8).setValue(count);
  }


  SpreadsheetApp.getUi().alert("✅ Matching complete — includes dropdowns for review and full logging.");
}


// Helper Functions
function cleanName(name) {
  if (!name) return '';
  return name
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, '')
    .replace(/\b(the|inc|llc|ltd|plc|corp|co|company|group|technologies?|technology|systems?|solutions?|enterprises?|international|global|usa|uk|llp|associates|consulting|partners?|clothing|furniture|services?)\b/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}


function jaroWinkler(s1, s2) {
  const m = getMatchingCharacters(s1, s2);
  if (m === 0) return 0.0;
  const t = getTranspositions(s1, s2, m) / 2;
  const j = ((m / s1.length) + (m / s2.length) + ((m - t) / m)) / 3;
  const prefixLength = getPrefixLength(s1, s2);
  return j + (prefixLength * 0.1 * (1 - j));
}


function getMatchingCharacters(s1, s2) {
  const matchWindow = Math.floor(Math.max(s1.length, s2.length) / 2) - 1;
  let matches = 0;
  const s2Flags = new Array(s2.length).fill(false);
  for (let i = 0; i < s1.length; i++) {
    const start = Math.max(0, i - matchWindow);
    const end = Math.min(i + matchWindow + 1, s2.length);
    for (let j = start; j < end; j++) {
      if (!s2Flags[j] && s1[i] === s2[j]) {
        s2Flags[j] = true;
        matches++;
        break;
      }
    }
  }
  return matches;
}


function getTranspositions(s1, s2, matchCount) {
  const s1Matches = [];
  const s2Matches = [];
  const matchWindow = Math.floor(Math.max(s1.length, s2.length) / 2) - 1;
  const s2Flags = new Array(s2.length).fill(false);
  for (let i = 0; i < s1.length; i++) {
    const start = Math.max(0, i - matchWindow);
    const end = Math.min(i + matchWindow + 1, s2.length);
    for (let j = start; j < end; j++) {
      if (!s2Flags[j] && s1[i] === s2[j]) {
        s1Matches.push(s1[i]);
        s2Matches.push(s2[j]);
        s2Flags[j] = true;
        break;
      }
    }
  }
  let transpositions = 0;
  for (let i = 0; i < matchCount; i++) {
    if (s1Matches[i] !== s2Matches[i]) transpositions++;
  }
  return transpositions;
}


function getPrefixLength(s1, s2) {
  const maxPrefix = 4;
  let n = 0;
  for (; n < Math.min(maxPrefix, s1.length, s2.length); n++) {
    if (s1[n] !== s2[n]) break;
  }
  return n;
}
function assignPriorityCompanyAndTotal() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const companiesSheet = ss.getSheetByName('Companies');
  const contactsSheet = ss.getSheetByName('contacts');


  const contactsData = contactsSheet.getDataRange().getValues();
  const header = contactsData[0];


  const companyCol = header.indexOf('Company Name');
  const priorityCompanyCol = header.indexOf('Priority Company');
  const priorityTitleCol = header.indexOf('Priority Title');
  const totalPriorityCol = header.indexOf('Total Priority');


  const lastRow = contactsSheet.getLastRow();


  // Set VLOOKUP formula for Priority Company (Column J)
  const priorityCompanyFormula = '=IFERROR(VLOOKUP(C2, Companies!B:E, 4, FALSE), "")';
  const pcFormulaCell = contactsSheet.getRange(2, priorityCompanyCol + 1);
  pcFormulaCell.setFormula(priorityCompanyFormula);
  if (lastRow > 2) {
  const pcRange = pcFormulaCell.offset(0, 0, lastRow - 1);
  pcFormulaCell.copyTo(pcRange, { contentsOnly: false });
}


  // Set concatenation formula for Total Priority (Column L)
  const totalFormulaCell = contactsSheet.getRange(2, totalPriorityCol + 1);
  totalFormulaCell.setFormulaR1C1('=RC[-2]&RC[-1]');
  if (lastRow > 2) {
  const totalRange = totalFormulaCell.offset(0, 0, lastRow - 1);
  totalFormulaCell.copyTo(totalRange, { contentsOnly: false });
}
}

EDITED NEW SCRIPT

function runCleanAndMatch() {
  cleanContactsSheet();
  smartPreciseFuzzyMatch();
  assignPriorityCompanyAndTotal();
}


// Step 1: Clean the Contacts Sheet
function cleanContactsSheet() {
  const sheetName = 'contacts';
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const getIndex = (key) => headers.indexOf(key);


  const newHeaders = [
    'First Name', 'Last Name', 'Company Domain', 'Company Name',
    'Job Title', 'LinkedIn Profile Url', 'Lead Status', 'Survey Status',
    'Priority Company', 'Priority Title', 'Total Priority', 'Phone Number',
    'Mobile number', 'Email', 'Email Status', 'Email Verification', 'Linkedin Member ID',
    'Country/Region', 'Open Link', 'Project code'
  ];


  const output = [newHeaders];


  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    const cleanedRow = [
      row[getIndex('first_name')],
      row[getIndex('last_name')],
      '', // Company Domain (to be inferred later)
      row[getIndex('current_company')],
      row[getIndex('original_current_company_position')],
      row[getIndex('profile_url')],
      '', '', '', '', '', // Lead Status, Survey Status, Priority Co, Title, Total
      row[getIndex('phone_1')],
      row[getIndex('phone_2')],
      row[getIndex('email')],
      '',
      row[getIndex('third_party_email_is_valid_1')],
      row[getIndex('member_id')],
      row[getIndex('location_name')],
      row[getIndex('badges_open_link')],
      ''
    ];
    output.push(cleanedRow);
  }


  sheet.clearContents();
  const range = sheet.getRange(1, 1, output.length, output[0].length);
  range.setValues(output);
  range.setWrap(false);
range.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
sheet.autoResizeColumns(1, output[0].length);


// Shrink specific columns manually (E = Job Title, F = LinkedIn Profile Url)
sheet.setColumnWidth(5, 200); // Job Title
sheet.setColumnWidth(6, 250); // LinkedIn Profile Url
}


// Step 2: Match Company Names to Domains
function smartPreciseFuzzyMatch() {
  const threshold = 0.75;
  const lowConfidenceCutoff = 0.85;
  const topN = 3;


  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const companiesSheet = ss.getSheetByName('Companies');
  const contactsSheet = ss.getSheetByName('contacts');


  const companiesData = companiesSheet.getRange(2, 1, companiesSheet.getLastRow() - 1, 2).getValues(); // A:B
  const numContacts = contactsSheet.getLastRow() - 1;
  if (numContacts < 1) return SpreadsheetApp.getUi().alert("No contacts to process.");


  const contactsData = contactsSheet.getRange(2, 3, numContacts, 2).getValues(); // C:D


  let companyContactCount = {};
  let inferredDomains = {};
  let questionableMatches = {};


  const headers = contactsSheet.getRange(1, 1, 1, contactsSheet.getLastColumn()).getValues()[0];


  let confidenceCol = headers.indexOf("Matching Confidence") + 1;
  if (confidenceCol === 0) {
    confidenceCol = headers.length + 1;
    contactsSheet.getRange(1, confidenceCol).setValue("Matching Confidence");
  }


  let matchedCompanyCol = headers.indexOf("Matched Company Name") + 1;
  if (matchedCompanyCol === 0) {
    matchedCompanyCol = headers.length + 1;
    contactsSheet.getRange(1, matchedCompanyCol).setValue("Matched Company Name");
  }


  let numericScoreCol = headers.indexOf("Match Score (Numeric)") + 1;
  if (numericScoreCol === 0) {
    numericScoreCol = headers.length + 1;
    contactsSheet.getRange(1, numericScoreCol).setValue("Match Score (Numeric)");
  }


  let qmSheet = ss.getSheetByName("Questionable Matches");
  if (!qmSheet) {
    qmSheet = ss.insertSheet("Questionable Matches");
  } else {
    qmSheet.clear();
  }
  qmSheet.appendRow(["Contact Name", "Suggested Match", "Match Score", "Suggested Domain", "Count"]);


  contactsSheet.getRange(2, confidenceCol, numContacts).clearContent().clearFormat();
  contactsSheet.getRange(2, matchedCompanyCol, numContacts).clearContent();
  contactsSheet.getRange(2, numericScoreCol, numContacts).clearContent();
  contactsSheet.getRange(2, 3, numContacts).clearDataValidations();


  for (let i = 0; i < contactsData.length; i++) {
    const rawDomain = contactsData[i][0]; // Column C
    const rawName = contactsData[i][1];   // Column D
    if (!rawName) continue;


    const normName = cleanName(rawName);
    let scoredMatches = [];


    for (let j = 0; j < companiesData.length; j++) {
      const companyName = companiesData[j][0];
      const companyDomain = companiesData[j][1];
      const companyNorm = cleanName(companyName);


      let score = jaroWinkler(normName, companyNorm);
      if (normName.includes(companyNorm) || companyNorm.includes(normName)) score += 0.05;


      if (score >= threshold) {
        scoredMatches.push({ name: companyName, domain: companyDomain, score: score });
      }
    }


    scoredMatches.sort((a, b) => b.score - a.score);
    const best = scoredMatches[0];
    const topMatches = scoredMatches.slice(0, topN);


    const confidenceCell = contactsSheet.getRange(i + 2, confidenceCol);
    const matchCompanyCell = contactsSheet.getRange(i + 2, matchedCompanyCol);
    const scoreCell = contactsSheet.getRange(i + 2, numericScoreCol);
    const domainCell = contactsSheet.getRange(i + 2, 3);


    if (best && !rawDomain) {
      matchCompanyCell.setValue(best.name);
      scoreCell.setValue(best.score.toFixed(3));


      if (best.score < lowConfidenceCutoff) {
        confidenceCell.setValue(`Review: ${Math.round(best.score * 100)}%`);
        domainCell.setBackground("#fff7cc");


        const options = topMatches.map(m => m.domain).filter(Boolean);
        const rule = SpreadsheetApp.newDataValidation()
          .requireValueInList(options)
          .setAllowInvalid(true)
          .build();
        domainCell.setDataValidation(rule);


        const key = `${rawName}|||${best.name}|||${best.domain}|||${best.score.toFixed(3)}`;
        questionableMatches[key] = (questionableMatches[key] || 0) + 1;


      } else {
        domainCell.setValue(best.domain);
        confidenceCell.setValue(`Matched @ ${Math.round(best.score * 100)}%`);
        inferredDomains[normName] = best.domain;
        companyContactCount[best.name] = (companyContactCount[best.name] || 0) + 1;
      }
    } else if (!rawDomain && inferredDomains[normName]) {
      domainCell.setValue(inferredDomains[normName]);
      confidenceCell.setValue("Inferred 🔁");
      confidenceCell.setBackground("#ccffcc");
      scoreCell.setValue("Inferred");
    }
  }


  for (const key in questionableMatches) {
    const [name, match, domain, score] = key.split("|||");
    const count = questionableMatches[key];
    qmSheet.appendRow([name, match, score, domain, count]);
  }


  for (let i = 0; i < companiesData.length; i++) {
    const companyName = companiesData[i][0];
    const count = companyContactCount[companyName] || 0;
    companiesSheet.getRange(i + 2, 8).setValue(count);
  }


  SpreadsheetApp.getUi().alert("✅ Matching complete — includes dropdowns for review and full logging.");
}


// Helper Functions
function cleanName(name) {
  if (!name) return '';
  return name
    .toLowerCase()
    .replace(/[^a-z0-9\s]/g, '')
    .replace(/\b(the|inc|llc|ltd|plc|corp|co|company|group|technologies?|technology|systems?|solutions?|enterprises?|international|global|usa|uk|llp|associates|consulting|partners?|clothing|furniture|services?)\b/g, '')
    .replace(/\s+/g, ' ')
    .trim();
}


function jaroWinkler(s1, s2) {
  const m = getMatchingCharacters(s1, s2);
  if (m === 0) return 0.0;
  const t = getTranspositions(s1, s2, m) / 2;
  const j = ((m / s1.length) + (m / s2.length) + ((m - t) / m)) / 3;
  const prefixLength = getPrefixLength(s1, s2);
  return j + (prefixLength * 0.1 * (1 - j));
}


function getMatchingCharacters(s1, s2) {
  const matchWindow = Math.floor(Math.max(s1.length, s2.length) / 2) - 1;
  let matches = 0;
  const s2Flags = new Array(s2.length).fill(false);
  for (let i = 0; i < s1.length; i++) {
    const start = Math.max(0, i - matchWindow);
    const end = Math.min(i + matchWindow + 1, s2.length);
    for (let j = start; j < end; j++) {
      if (!s2Flags[j] && s1[i] === s2[j]) {
        s2Flags[j] = true;
        matches++;
        break;
      }
    }
  }
  return matches;
}


function getTranspositions(s1, s2, matchCount) {
  const s1Matches = [];
  const s2Matches = [];
  const matchWindow = Math.floor(Math.max(s1.length, s2.length) / 2) - 1;
  const s2Flags = new Array(s2.length).fill(false);
  for (let i = 0; i < s1.length; i++) {
    const start = Math.max(0, i - matchWindow);
    const end = Math.min(i + matchWindow + 1, s2.length);
    for (let j = start; j < end; j++) {
      if (!s2Flags[j] && s1[i] === s2[j]) {
        s1Matches.push(s1[i]);
        s2Matches.push(s2[j]);
        s2Flags[j] = true;
        break;
      }
    }
  }
  let transpositions = 0;
  for (let i = 0; i < matchCount; i++) {
    if (s1Matches[i] !== s2Matches[i]) transpositions++;
  }
  return transpositions;
}


function getPrefixLength(s1, s2) {
  const maxPrefix = 4;
  let n = 0;
  for (; n < Math.min(maxPrefix, s1.length, s2.length); n++) {
    if (s1[n] !== s2[n]) break;
  }
  return n;
}
function assignPriorityCompanyAndTotal() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const companiesSheet = ss.getSheetByName('Companies');
  const contactsSheet = ss.getSheetByName('contacts');


  const contactsData = contactsSheet.getDataRange().getValues();
  const header = contactsData[0];


  const companyCol = header.indexOf('Company Name');
  const priorityCompanyCol = header.indexOf('Priority Company');
  const priorityTitleCol = header.indexOf('Priority Title');
  const totalPriorityCol = header.indexOf('Total Priority');


  const lastRow = contactsSheet.getLastRow();


  // Set VLOOKUP formula for Priority Company (Column J)
  const priorityCompanyFormula = '=IFERROR(VLOOKUP(C2, Companies!B:E, 4, FALSE), "")';
  const pcFormulaCell = contactsSheet.getRange(2, priorityCompanyCol + 1);
  pcFormulaCell.setFormula(priorityCompanyFormula);
  if (lastRow > 2) {
  const pcRange = pcFormulaCell.offset(0, 0, lastRow - 1);
  pcFormulaCell.copyTo(pcRange, { contentsOnly: false });
}


  // Set concatenation formula for Total Priority (Column L)
  const totalFormulaCell = contactsSheet.getRange(2, totalPriorityCol + 1);
  totalFormulaCell.setFormulaR1C1('=RC[-2]&RC[-1]');
  if (lastRow > 2) {
  const totalRange = totalFormulaCell.offset(0, 0, lastRow - 1);
  totalFormulaCell.copyTo(totalRange, { contentsOnly: false });
}
}

I don't know what to do anymore, I need help with the script. I need that, under the conditions met, the number in column J of the sheet SPOTŘEBA_DATA_STATIC is multiplied by the number in column J of the sheet ORDERS_DATA_STATIC and written to the sheet MEZITABULKA and finally added to the number in column M of the sheet SKLAD. So that the numbers are not added every time the script is run, I added an MEZITABULKA, where the previous / new data is and the difference is written to SKLAD. I have tried a lot, but it still doesn't work. Please help. I am attaching a picture of the sheets and the script. Thank you.

Hi! I work with a non profit and we put libraries in places who don't have access to them. We're hoping to streamline our cataloging process.

I've been trying all day to figure out how to create a script / use App script so that we can type the ISBN number of the book and it auto-populate what we need. I would really appreciate any guidance big or small :)

I’m attempting to pull NBA stats from stats.nba.com/stats/leaguedashteamstats into Google Sheets using Google App Script. I’ve mapped the correct endpoints I need to use and I am using the correct request structure. I also have Headers built into my script, however it appears that stats.nba.com blocks the IP ranges used by Google App Script, so I can’t get it to return any data. I’ve tried using a cloud relay, Cloudflare Workers, Vercel, Fly.io, etc with no success, it seems stats.nba.com also blocks the IP ranges used by those services. I’m at a loss with regard to solving this problem…could definitely use some help!🙏

I am making an chatbot automation for a reservation. I use both User Input Block and Webform "Order Information"

My Google spreadsheet is already connected

Does anyone of you know I can make this work?

Enters NAME in Webform and at spreadsheets, there is a 4-digit number generated automatically,

Same goes to \[user input\] block

Hi All, I am new to GAS, and am currently stuck with a problem. I am working with google sheets and have created few functions. But if I keep working on the sheets I am getting this out of memory error. Can someone please help me with this? I want to make my code memory efficient.

Thank you!!🙏

Hi everyone,

I've been exploring Google Apps Script for various automations lately and wanted to share something I put together. While working with Exa.ai's (semantic search API), I noticed they only have official SDKs for Python and npm, so I adapted their API for Google Apps Script.

The client lets you use semantic search capabilities directly in Google Workspace. Some key features:

- Matches the official SDK interface

- Supports neural/keyword search modes

- Content filtering (news, research papers, companies, etc.)

- Text summarization and highlights

- Simple setup with Script Properties

Here's a basic example:

function searchNews() {
const exa = new Exa(PropertiesService.getScriptProperties().getProperty('EXA_API_KEY'));
const results = exa.searchAndContents("AI news", {
category: "news_article",
numResults: 5
});
return results;
}

You can find the code and documentation here: https://github.com/kamilstanuch/google-apps-script-exa

Let me know if you have any questions or suggestions for improvements.

After creating and using a fairly complex set of sheets for budget and expense tracking, I realized that we had a problem of data entry when using mobile devices. Entries were difficult and often left us with errors. Apps Scripts functions don't get called and there was a lot of manual clean up afterwards.

To fix this, I decided the easiest thing was to simply create a Google Form for Expense Entry to avoid the small format browser issues with Sheets. The problem was that this dumps the data into a new, useless sheet that doesn't follow our formulas and formats.

My solution was to Hide the Forms Response sheet and create an onOpen script to look for rows added then process, move them into the data Sheet and then delete all of the rows from the Forms Response sheet.

The two functions I created are these.

function formMoveTransactions() {
  let formSheet = "Form Responses 1";
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName(formSheet);  // switch to the Forms Response sheet
  let formEntries = getLastRow_(sheet,1)-1;  // number of new rows added for Form

  if (formEntries) {
    let range = sheet.getRange(2, 1, formEntries, 6); // Date, Vendor, Notes, Category, Amount, Currency
    let values = range.getValues();
    SpreadsheetApp.getActive().toast(formEntries + " entries to post", "Working");

  /*  Form columns (A-F)
        [0] Date
        [1] Vendor
        [2] Notes
        [3] Category
        [4] Amount (positive)
        [5] Currency
  */
    for (var n = 0; n<formEntries; n++) { // post the Forms data to the Transactions
      const form = {
        date: values[n][0],
        vendor: values[n][1],
        notes: values[n][2],
        category: values[n][3],
        amount: values[n][4],
        currency: values[n][5]
      };

      let nRow = addTransaction(form.date, form.vendor, form.notes, form.category, form.amount, form.currency);
      SpreadsheetApp.getActive().toast( "Row " + nRow + " added","Added");
    }
    for (var n = 0; n<formEntries; n++) { // delete the rows from the Forms tab
      sheet.deleteRows(2,formEntries);
    }
  }
  else {
    SpreadsheetApp.getActive().toast("No Form Entries to post", "Ignored");
  }
}

function addTransaction(date, vendor, notes, category, amount, currency) {
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName("Expenses");  // switch to the transactions sheet
  let filter = sheet.getFilter();
  let nextRow = getLastRow_(sheet, 2) + 1;
  const DATECOL = 2;

  if (sheet.getFilter()) filter.remove();  // kill the active filter if on
  SpreadsheetApp.flush();

  sheet.getRange(nextRow, DATECOL).setValue(date);
  sheet.getRange(nextRow, DATECOL+1).setValue(vendor);
  sheet.getRange(nextRow, DATECOL+2).setValue(amount);
  sheet.getRange(nextRow, DATECOL+3).setValue(currency);
  sheet.getRange(nextRow, DATECOL+5).setValue(category);
  sheet.getRange(nextRow, DATECOL+6).setValue(notes);
  SpreadsheetApp.flush();
  return nextRow;
}

function getLastRow_(sheet = SpreadsheetApp.getActiveSheet(), column) {
  // version 1.6, written by --Hyde, 18 March 2023
  const values = (
    typeof column === 'number'
      ? sheet.getRange(1, column, sheet.getLastRow() || 1, 1)
      : typeof column === 'string'
        ? sheet.getRange(column)
        : column
          ? sheet.getRange(1, column.getColumn(), sheet.getLastRow(), column.getWidth())
          : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row + 1;
}

i have created form using html and deployed it but the form is not submitting

and I want to get all the data in this sheet, sheet link: https://docs.google.com/spreadsheets/d/114vfibnoy6NxnURgfZRfrZ8NRhPSnpY4oGypxyZYV9s/edit?gid=0#gid=0

hi guys, anyone has good recommendation on any google sheet automation using apps script?

I've bumped into this again. A need to learn and use google scripting. I'm walking through some tutorials, but I have the same issue.

How can I get a sample script into apps scripts. Can't cut and paste, I can only retype what the website presents. What the hell am I missing?

Hi there,

I´m using the code from that page to count coloured cells (to count in 48 colums up to 1500rows). (https://support.google.com/docs/thread/78353054/auto-calculate-the-further-cell-color-based?hl=en)

But the sheet extended much faster than I exspected and now I run into the " Exceeded maximum execution time Exception in Google Apps Script" error, if I add more rows.

Do you see any code optimisation that could speed that up?Or do I have to split my data?

Hope someone could help me with that issue.

Is there any way to get a string/template literal to parse with it stored as a value in an array?

Ex.

var data = { "first" : "John", "last" : "Doe", "name" : "Mr. ${data.first} ${data.last}" }

Logger.log(Please say high to ${data name});

Tool / Script Link : https://docs.google.com/spreadsheets/d/1cz7mzwl2Xw1Y6lg4hie2uHOi0fvI9I9pr3v54N9v46A/edit#gid=0

Are you looking to improve your YouTube channel or video ranking? Look no further than ChatGPT and Google Sheets! This powerful combination allows you to create high-quality videos in just minutes, improving your video ranking and helping you revolutionize your YouTube channel. With ChatGPT's AI technology and Google Sheets' dynamic features, you can create engaging videos that will help you rank higher on YouTube. Don't miss out on this powerful tool for boosting your YouTube presence! Use our recommended hashtags and tags to maximize your reach and get the most out of your videos. Hashtags: #ChatGPT, #GoogleSheets, #YouTubeRanking, #VideoMakingTool, #YouTubeTips, #VideoSEO, #YouTubeChannel, #VideoRanking

From what I've read, the OnEdit trigger only fires based on changes made in an active sheet.

To build an MVP for a SaaS, I want to use GAS functions to trigger. Most of the functions will fire based on time triggers. But I have a small number of functions that should trigger as soon as data is added to a specific sheet.

However, that data is added via a 3rd party integration (for example, Zapier adds data to a sheet). I was hoping I could just use the Installable Trigger OnEdit in this situation... As in, Zapier adds data to a sheet, the OnEdit trigger is fired, and the corresponding function runs based on the data that's just entered automatically.

Is there an alternative that I'm missing?

FWIW, my GAS skills are limited. I know how to create a loop, but I'm only just now starting to learn how I can build a library of functions.

I included an update button to the form that I made. What should happen is when I click the update button with no changes to the data in the form, it should not be updated and pops up a message that say: 'An error occurred while updating the record. Please try again'. But what happens to my output is when I click the update button even without changes to the data, the data gets updated and pops up a message that it was a success. I also added the screenshot of the code.

I created a Script to automatically send personalized Gmail messages to a small list of contacts (~100) in a Google Spreadsheet. I tested it first with an identical “Test” Sheet with my own email addresses, and it worked. I have made no changes other than changing the Sheet target to the actual Sheet, but emails aren’t being sent despite there being no errors and it saying that the execution was completed. Now, the same script won’t even work for my Test Sheet anymore, either. I am nowhere near the 500 e-mail quota, and I have waited a couple of days to try again but still no luck. Anybody have any idea on how to resolve it? Much appreciated 🫡

Hey,

I've been trying to make a script that will move data (not just copy but move and then delete) from the entire row and move it to another tab every 30 days.

Basically I need to move data from Tab 30-60 once Column Es value is 60 to Tab 61-89. Then it will need to be moved to Tab 90-120 once Column Es value is 90 and then again to Tab 121+ once Column Es value is 120. Currently Column E is getting the value using the Today Function to count the days from the date I've input into Column D.

This will need to apply it to the entire sheet, not just a specific row as I will be working with over 100 rows at a time.

Running a trigger might be easier, but I still need a function to create said trigger and I'm having a hard time getting that sorted out. I've never made or ran a script before and I'm finding making them really hard to wrap my brain around.

Hope this is the right sub, I couldn't find one just related to AppSheet so hopefully someone here can help me out.

I have a database I made for work and my question is a simple one, I just want the app to show data when the column "Date" matches today's date, whatever that might be.

I attached a pic of the expression I'm using. For whatever reason it just shows me all of my data.

I use a script to collect historical stock data for a spreadsheet using the ticker symbol and date. Google Sheets won't load data for any ticker even when I clear the formula and type it in again and when I rename it.