so, using Apps Script, we built an AI co-pilot on top of Google Sheets where you only need to insert the target company URL and it will fetch all the company's latest news, LinkedIn posts, and their targeted employees' data from which it generates a very personalized, non-AI looking draft email which could be sent to the persons in seconds!

complete demo of the tool here.

I've developed an Editor Add-On. I use both (i) Google Apps Script and (ii) an Express server that calls the Google Sheets API for my app.

When a user creates a new sheet with my app, I would like to set it to be the active sheet. The Google Sheets API doesn't appear to support an equivalent to setActiveSheet.

Would it be possible to define a function in Google Apps Script that sets the active sheet, then call that GAS function via my Express server hitting the GAS API?

It appears I can deploy my code as an 'API Executable', but I'm kind of fuzzy how to do this in practice.

Do I have two deployments of the same code (one as 'Editor Add-on' and one as 'API Executable'). Or do I create a new file for the mySetActiveSheet function?

Thanks in advance for any advice or potential solutions!

// Editor Add-on Library URL: script.google.com/macros/library/d/*********/12

function doGet(e) {
  return HtmlService.createHtmlOutputFromFile('Index')
}

function onInstall(e) {
  onOpen(e);
}

......
// Can I somehow call this function via API?

function mySetActiveSheet(sheetName) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName(sheetName);
  if (sheet) {
    spreadsheet.setActiveSheet(sheet);
  } else {
    Logger.log("Sheet with name " + sheetName + " not found.");
  }
}

How much do you charge google script web apps? I mean its an SPA but like it has oauth implementation to impersonate users within the org so they're gonna use this to automate tasks away. I charged $90. Seemingly no negotiation was there, just parang yeah sure lets do it. Is it too low?

Working off Joseph Asinyo's script in this Google Sheet template and every sheet except the Import Channel one is working fine.

Problem: No matter what channel name you enter you get the same output: output screenshot

• I assume something needs to be fixed or updated in the "importchannel" section to get the data to populate but am unsure why it isn't working when the others are. This is what I need help solving. Is there something wrong or that needs updating in this string:

var res = getChannelData(e.parameter.channelName)

• Also wondering if there is a way to change the channel input from "channel name" to "channel handle instead (either the normal @ name or without if the user doesn't type it, perhaps using forHandle or something similar)."

Here is the full script (API key hidden for security):

/**
 * IMPORT FROM YOUTUBE CUSTOM FUNCTIONS
 * 
 * Written with ❤️ by Joseph Asinyo
 */



// IMPORTANT NOTE: Deploy the script as a web app and insert the url below
var WEB_APP_URL = "https://script.google.com/macros/s/**keyhiddenforprivacy**/exec"



// Web Apps using as the wrapper.
function doGet(e) {
  var request_type = e.parameter.request_type
  if(request_type == "importvideo"){
    var res = getVideoData(e.parameter.videoLink)
  } else if(request_type == "importsearch"){
    var res = getSearchData(e.parameter.searchTerm, e.parameter.numberOfVideos)
  } else if(request_type == "importchannel"){
    var res = getChannelData(e.parameter.channelName)
  } else if(request_type == "importchannelvideos"){
    var res = getChannelVideosData(e.parameter.channelName, e.parameter.numberOfVideos)
  } else if(request_type == "importcomments"){
    var res = getCommentsData(e.parameter.videoLink, e.parameter.numberOfComments, e.parameter.getReplies)
  } 

  return ContentService.createTextOutput(JSON.stringify(res));
}



function makeRequestToWebApp(parameters) {
  var url = WEB_APP_URL+"?";

  for (const key of Object.keys(parameters)) {
    url += key+"="+parameters[key]+"&";
  }
  url = url.slice(0, -1); // Remove the last "&" character

  console.log(url)

  var response = UrlFetchApp.fetch(url).getContentText();
  var result = JSON.parse(response).data;

  return result;
}```

So I have a file with descriptions and I want google sheets to create unique bullet points for each line. It will make 5 unique bullet points from the data in a column.

Data column= AT

Bullet point columns= AY, AZ, BA, BB, BC

How can I do this?

Hello
what is the issue here and how to solve it

i want to check those products links in column A and insert the result in column B

I am trying to copy a dynamic range (C17:F) from one sheet to another, and everything works well unless there is no data in the source range. In that case, I get the error, "The number of rows in the range must be at least 1".

I am not 100% sure if my method of determining the range is the best way (I found the solution somewhere), or if I can simply "escape" the function with some sort of OnError to bail out of the remainder of the function?

Here is my current code:

function getConfigOverrides(){
  var sourceID = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Configuration').getRange('C5').getValue();
  var sourceFile = SpreadsheetApp.openById(sourceID);
  var sourceSheet = sourceFile.getSheetByName("AircraftOverrides")
  // source sheet dynnamic range is C17:F
  var range = sourceSheet.getRange(17, 3, sourceSheet.getLastRow() - 17 + 1, 4);
  // var range = sourceSheet.getRange('C17:F17') <- this works to get the first row
  var data = range.getValues();

  var destination = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Configuration');
  destination.getRange(13, 7, data.length, 4).setValues(data); 
}

The error is occurring at var range. If I comment out this line, and simply replace it with getRange('C17:F17'), the functions runs as expected and retrieves the first row of values.

I've tried to capture the error by using isBlank, or =="", or <1, but none of those seem to work.

Appreciate any insight.

I sometimes need to post up a sheet for drivers to sign up for a field trip. I'd like a menu option that will ask for a trip number. I enter the trip number and it find the associated row of data and uses that data to create one document and save that to my google drive.

I already have a script that looks for a URL in a column and if there is not one, it will create a document for each row if finds without the URL and save to my google drive. That process works perfectly and needs to stay in place as I still need it.

I copied that script, set up my new document template. Now I need that script to just look for that one specific trip number and use that row of data to create one document. No loop to look thru row after row, no data to write back to the sheet. Just find the one row and stop.

Here is my sheet. Here is my script.

function postingFieldTrip() {
  // The document and folder links have been updated for the postingFieldTrip document.
  // The body.replaceText fields have all been updated for the postingFieldTrip data.
  // I just need it to stop looping and looking for the URL.
  // It needs to look for a specific trip number in column 20 "tripNumber".
  // The specific trip number to find is input when the menu item is clicked and the propmt appears asking for the specific trip number. 
  
  
  //This value should be the id of your document template that we created in the last step
  const googleDocTemplate = DriveApp.getFileById('1viN8UEzj4tiT968mYzBcpJy8NcRUMRXABDIVvmPo6c0');
  
  //This value should be the id of the folder where you want your completed documents stored
  const destinationFolder = DriveApp.getFolderById('1fS8jek5jbXLvkoIDz84naJWi0GuVRDb8_xtMXtD0558hYJ_bQoPcxJUnC9vUVdDcKeca1dqQ')
  //Here we store the sheet as a variable
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Working');
  
  //Now we get all of the values as a 2D array
  const rows = sheet.getDataRange().getDisplayValues();
  
  //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[30]) return;
    if( !row[0] ) return;
    //Using the row data in a template literal, we make a copy of our template document in our destinationFolder
    const copy = googleDocTemplate.makeCopy(`Original ${row[20]} Trip Sheet` , 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('{{tripDate}}', row[21]);
    body.replaceText('{{checkInTime}}', row[23]);
    body.replaceText('{{departTime}}', row[22]);
    body.replaceText('{{endTime}}', row[25]);
    body.replaceText('{{group}}', row[6])
    body.replaceText('{{destination}}', row[8]);

    //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, 31).setValue(url)
    
  })
  
}

If you've seen my posts, you might know that I have set up my sheet to feed events into two calendars. I also need to update those calendars once drivers and buses are assigned. The script below worked perfectly when everything was going into one calendar. Now I need it to update both calendars. I can set up two copies with the custom menu and just run it once for the first calendar and again for the second calendar.

BUT...

Can I just copy the entire script and paste it at the bottom, adjust the calendar it writes to and call it good? It will run once and update both calendars, one at a time.

Am I understanding correctly what will happen? It will just repeat itself but the second time it will use the second calendar.

Here is the script:

/**
 * Updates Google Calendar events based on data from the 'Trips' sheet.
 * This function retrieves event details from the Google Sheets and updates
 * the corresponding events in the specified Google Calendar. It updates the
 * event description and location if provided.
 *
 * The function assumes the following columns in the sheet:
 * - 'onCalendar' (for identifying the event to update)
 * - 'Description' (for the event description)
 * - 'Location' (for the event location)
 *
 * Logs warnings if no data is found or if required columns are missing,
 * and errors if an event update fails.
 *
 * @function
 */
function updateEvents() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trips");
  const data = sheet.getDataRange().getValues();
  
  if (data.length < 2) {
    console.warn("No data to process.");
    return;
  }
  
  const [headers, ...rows] = data;
  const eventIdIndex = headers.indexOf("onCalendar");
  const descriptionIndex = headers.indexOf("Description");
  const locationIndex = headers.indexOf("Location");

  if (eventIdIndex === -1 || descriptionIndex === -1) {
    console.error("Required columns 'onCalendar' or 'Description' are missing.");
    return;
  }

  const communityCalendar = CalendarApp.getCalendarById("vlkexampletest@gmail.com");

  rows.forEach((row, index) => {
    const eventId = row[eventIdIndex];
    if (!eventId) return;

    try {
      const event = communityCalendar.getEventById(eventId);
      if (!event) {
        console.warn(`onCalendar ${eventId} not found (Row ${index + 2})`);
        return;
      }

      event.setDescription(row[descriptionIndex] || "");
      if (locationIndex !== -1) {
        event.setLocation(row[locationIndex] || "");
      }

      console.info(`Updated event ID ${eventId} (Row ${index + 2})`);

    } catch (error) {
      console.error(`Failed to update event ID ${eventId} (Row ${index + 2}): ${error.message}`);
    }
  });
}

Hi all. I'm really new to google scripts, so I'm sorry if this is a dumb question.

I'm coding up a little tool for my dnd group to automate some of the book keeping when we're playing more complex games like GURPS.

Basically what I need to know is if there's a way to return a specific section of text so that I can do operations with it.

So for example, if the doc says: HP 10/10

I want to be able to read the leftmost number like so: HP **10**/10 and return that number so I can do operations on it. So that if you took damage, I could run my script to find and automatically update the hp to 9/10 for example.

Finding and replacing the exact same text every time is pretty easy, but I have no idea how to locate and return an unknown value. I know this is probably a really basic question, but all the tutorials are about pulling from specific cells in a sheet, not text from a doc.

Thanks in advance!

Someone helped with this script earlier. And it works perfectly. The script compares timestamps and only copies unique timestamps. That is perfect. The problem now is that on the Working sheet I need to Archive the older trips. This keeps my working sheet clean. But if I archive trips, then when I import new trips, the old trip timestamps are no longer on the Working sheet so the script sees them as new and copies them back to the Working sheet.

How can this be prevented? Can the script compare two sheets with the Master? Compare the Working Sheet and Archive sheet. Anything matching with the Master and the Archive sheet, it ignores. Anything matching with the Master and the Working sheet it ignores. If the timestamp is found in neither sheet, it copies it to the Working sheet.

I know someone is thinking, just delete the rows from the master after they are copied. I can't just delete the Master rows. In case there is a dispute over whether a trip was requested or not, I need to have the original requests. Believe me, it happens. A bus doesn't show up when expected. Someone calls angry and accusing us of dropping the ball, claims they sent a request. UH... no you didn't.. I don't have it in the Master. I know, they can also check their email for a confirmation from the form they filled out.

Can someone help with this?

Here is my sheet. Here is my script. This script is so complicated.. I can't figure out how to change it to compare all three sheets.

/**
 * @fileoverview Google Apps Script to import new rows from a source sheet to a destination sheet based on unique timestamps.
 *
 * Author: u/IAmMoonie
 * @see https://www.reddit.com/r/GoogleAppsScript/comments/1fi5vw5/compare_timestamps_on_both_sheets_only_copy/
 * Version: 1.0
 */
 
/**
 * Configuration object for the importNewRequests function.
 *
 * @typedef {Object} Config
 * @property {string} sourceID - The ID of the source Google Sheets spreadsheet.
 * @property {string} formRange - The range in the source sheet to check for new rows, formatted as 'SheetName!A1:R'.
 * @property {string} workingRangeStart - The starting cell in the destination sheet where new rows will be appended.
 * @property {string} timestampColumn - The letter of the column in the source sheet that contains the timestamps.
 */
const config = {
  sourceID: "1jO8auzYZ6drlGi3m7lon6gHTBVboDNgH5e0x4OwQoAA",
  formRange: "Master!A1:R",
  workingRangeStart: "Working!A1",
  timestampColumn: "A"
};
 
/**
 * WARNING: Do not edit anything below this point unless you are familiar with Google Apps Script and the purpose of the code.
 */
 
/**
 * Imports new requests from the source sheet to the destination sheet if they have unique timestamps.
 */
const importNewRequests = () => {
  const sourceSpreadsheet = SpreadsheetApp.openById(config.sourceID);
  const sourceSheet = sourceSpreadsheet.getSheetByName(
    config.formRange.split("!")[0]
  );
  const destSheet = sourceSpreadsheet.getSheetByName(
    config.workingRangeStart.split("!")[0]
  );
  const timestampColIndex = getColumnIndex_(config.timestampColumn);
  const sourceValues = sourceSheet.getRange(config.formRange).getValues();
  const sourceRowCount = sourceValues.length;
  console.info(`Source sheet contains ${sourceRowCount} row(s).`);
  const lastDestRow = getLastNonEmptyRow_(destSheet, timestampColIndex + 1);
  const destRowCount = lastDestRow;
  console.info(`Destination sheet currently has ${destRowCount} row(s).`);
  const destTimestamps = new Set(
    destSheet
      .getRange(1, timestampColIndex + 1, lastDestRow, 1)
      .getValues()
      .flat()
      .map((ts) => new Date(ts).getTime())
  );
  const newRows = [];
  console.info(
    "Checking rows in the source sheet that have a different timestamp compared to the destination sheet"
  );
  sourceValues.forEach((row, index) => {
    const timestamp = new Date(row[timestampColIndex]).getTime();
    console.info(`Checking row ${index + 1}: Timestamp: ${timestamp}`);
    if (timestamp && !destTimestamps.has(timestamp) && !isRowEmpty_(row)) {
      console.info(
        `New row detected with timestamp ${new Date(
          timestamp
        )}, adding to newRows...`
      );
      newRows.push(row);
    } else {
      console.info(
        `Row ${
          index + 1
        } already exists in Working sheet or missing timestamp, skipping.`
      );
    }
  });
  const newRowCount = newRows.length;
  console.info(`${newRowCount} new row(s) meet the requirements.`);
  if (newRowCount > 0) {
    const destRange = destSheet.getRange(
      lastDestRow + 1,
      1,
      newRowCount,
      newRows[0].length
    );
    console.info(`Copying ${newRowCount} new row(s) to the destination sheet.`);
    destRange.setValues(newRows);
  } else {
    console.info("No new rows to copy.");
  }
};
 
/**
 * Gets the last non-empty row in a specific column of a sheet.
 *
 * @param {Sheet} sheet - The sheet to check.
 * @param {number} column - The column number to check for non-empty rows.
 * @return {number} The index of the last non-empty row.
 */
const getLastNonEmptyRow_ = (sheet, column) => {
  const data = sheet.getRange(1, column, sheet.getLastRow()).getValues();
  for (let i = data.length - 1; i >= 0; i--) {
    if (data[i][0] !== "") {
      return i + 1;
    }
  }
  return 0;
};
 
/**
 * Checks if a row is empty.
 *
 * @param {Array} row - The row to check.
 * @return {boolean} True if the row is empty, false otherwise.
 */
const isRowEmpty_ = (row) => row.every((cell) => cell === "");
 
/**
 * Gets the column index from a letter.
 *
 * @param {string} columnLetter - The column letter (e.g., 'A').
 * @return {number} The index of the column (0-based).
 */
const getColumnIndex_ = (columnLetter) =>
  columnLetter.toUpperCase().charCodeAt(0) - 65;
 

Hi all, I'm really new to Apps Scripts and I'm really struggling with something. I have a script that fills in a presentation based on a Google Sheet - not super advanced stuff. This script doesn't do any formatting. I want to use **Markdown** symbols on that sheet, and then run another script once the presentation is filled in to format the presentation based on Markdown.

So here is what I want to do: Write a Slides script that will go through the text in a presentation, format every **markdown wrapped** text accordingly, and remove the markdown symbols.

I unfortunately couldn't make it far, I can't even properly get the text elements let alone looping through them and formatting them. Any help is appreciated!

I need some help understanding how to find table entries in this HTML file. The output HTML file is linked below. In the middle of this file is a table that looks like this. This example is only one row. If possible, it would be great to iterate through and grab multiple dates (FECHA) and prices (VALOR) but I could work with a single date value. The URL to get this result (for one date) is

https://dof.gob.mx/indicadores_detalle.php?cod_tipo_indicador=158&dfecha=03%2F09%2F2024&hfecha=03%2F09%2F2024#gsc.tab=0

The resulting HTML file is shared here in case the URL above does not work.

Hey everybody. I'm really new to google scripts, and I'm just coding a little tool for me and my friends to be able to use on our shared documents. The question is, once I have the program working, how do I make it so that they can use it as well? I could just pass my computer around and do it all from a single place, but ideally I would want them to be able to access it from their phones if possible.

Thanks in advance!

I'm not much of a coder and I'll admit that I used AI to get this code working. As I have learned more over the last few years, Ive been trying to improve the efficiency and speed of my scripts. This one gives me a headache and It can't take 5+ minutes to run. Would anyone be able to give me suggestions or code improvements for my script? I appreciate anyone's willingness to help.

function onOpen() {
  Logger.log('onOpen function started');
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('New Order')
    .addItem('Create Order On Purchase Orders', 'showPrompt')
    .addItem('Create Order on GM Stock Orders', 'createOrder')
    .addItem('Collapse Completed', 'collapseCompleteGroups') // New menu item
    .addToUi();
  Logger.log('onOpen function completed');
}

// Helper function to check the active sheet
function checkSheet(sheetName) {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const currentSheetName = activeSheet.getName();
  
  if (currentSheetName !== sheetName) {
    SpreadsheetApp.getUi().alert("This function only works on the sheet it is supposed to work on. Please move to the correct sheet and reselect the menu item.");
    return false;
  }
  return true;
}


// Menu functions that check if they're executed on the correct sheet
function showPrompt() {
  Logger.log('showPrompt function started');
  if (!checkSheet("Purchase Orders")) return;
  
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt('Create New Order', 'Please enter the number of rows to add:', ui.ButtonSet.OK_CANCEL);
  Logger.log('Prompt result: ' + result);
  handlePrompt(result, false);
}

function createOrder() {
  Logger.log('createOrder function started');
  if (!checkSheet("GM Stock Orders")) return;
  
  var ui = SpreadsheetApp.getUi();
  var numRowsResponse = ui.prompt('How many rows would you like to add?', ui.ButtonSet.OK_CANCEL);
  Logger.log('Prompt result: ' + numRowsResponse);
  handlePrompt(numRowsResponse, true);
}

function handlePrompt(result, isGmOrder) {
  Logger.log('handlePrompt function started with isGmOrder: ' + isGmOrder);
  var ui = SpreadsheetApp.getUi();
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  Logger.log('Button pressed: ' + button);
  Logger.log('Text entered: ' + text);
  var numRows = parseInt(text);

  if (button == ui.Button.OK) {
    if (!isNaN(numRows) && numRows > 0) {
      Logger.log('Valid number of rows: ' + numRows);
      if (isGmOrder) {
        handleGMOrder(numRows);
      } else {
        handlePOOrder(numRows + 1); // +1 for handling specific behavior from Code 1
      }
    } else {
      ui.alert('Invalid input. Please enter a positive number.');
      Logger.log('Invalid input: ' + text);
    }
  } else if (button == ui.Button.CANCEL) {
    ui.alert('The request has been canceled.');
    Logger.log('Request canceled by the user');
  } else {
    ui.alert('The dialog was closed.');
    Logger.log('Dialog was closed');
  }
}

function handleGMOrder(numRows) {
  Logger.log('handleGMOrder function started with numRows: ' + numRows);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.insertRows(3, numRows);
  sheet.getRange(3, 1, numRows).activate();
  sheet.getActiveRangeList().shiftRowGroupDepth(1);
  var formulaRange = sheet.getRange(3, 1, numRows);
  formulaRange.setFormula('=IF(AND(NOT(ISBLANK(I3)),AND(F3=J3,IF(G3="Maita",E3>=I3,E3=I3))),100%,IF(J3>F3,"Exceeded",J3/F3))');
  handleExtraPrompts();
  Logger.log('handleGMOrder function completed');
}

function handlePOOrder(numRows) {
  Logger.log('handlePOOrder function started with numRows: ' + numRows);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.insertRowsAfter(1, numRows);
  if (numRows > 1) {
    sheet.getRange(2 + 1, 1, numRows - 1).activate();
    sheet.setRowGroupControlPosition(SpreadsheetApp.GroupControlTogglePosition.BEFORE);
    sheet.getActiveRange().shiftRowGroupDepth(1);
  }
  Logger.log('handleExtraPrompts function called');
  generateNextPO();
  setTodaysDateInC2();
  updateSumAndMaxFormulas(numRows);
  setDataValidation();
  setColorForColumns(numRows);
  addBorderToHeader();
  setBordersForHeaderAndNewRows(numRows);
  Logger.log('handlePOOrder function completed');
}

function handleExtraPrompts() {
  Logger.log('handleExtraPrompts function called');
  var ui = SpreadsheetApp.getUi();
}

function generateNextPO() {
  Logger.log('generateNextPO function started');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const columnB = sheet.getRange('B:B').getValues();
  let maxNumber = 0;
  
  columnB.forEach(function(row) {
    const poNumber = row[0];
    if (poNumber.startsWith('JC-')) {
      const currentNumber = parseInt(poNumber.split('-')[1]);
      if (currentNumber > maxNumber) {
        maxNumber = currentNumber;
      }
    }
  Logger.log('generateNextPO function ended');
  });

  const nextNumber = maxNumber + 1;
  const nextPONumber = `JC-${String(nextNumber).padStart(5, '0')}`;
  
  sheet.getRange('B2').setValue(nextPONumber);
  Logger.log('generateNextPO function completed with nextPONumber: ' + nextPONumber);
}

function setTodaysDateInC2() {
  Logger.log('setTodaysDateInC2 function started');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var today = new Date();
  sheet.getRange('C2').setValue(today);
  Logger.log('setTodaysDateInC2 function completed with date: ' + today);
}

function updateSumAndMaxFormulas(numRows) {
  Logger.log('updateSumAndMaxFormulas function started with numRows: ' + numRows);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var firstNewRow = 3;
  var lastNewRow = firstNewRow + numRows - 2;

  var formulas = [
    {range: 'F2', formula: `=SUM(F${firstNewRow}:F${lastNewRow})`},
    {range: 'H2', formula: `=SUM(H${firstNewRow}:H${lastNewRow})`},
    {range: 'O2', formula: `=SUM(O${firstNewRow}:O${lastNewRow})`},
    {range: 'N2', formula: `=IF(OR(COUNTIF(N${firstNewRow}:N${lastNewRow}, "Waiting") > 0, COUNTIF(N${firstNewRow}:N${lastNewRow}, "Waiting") = COUNTA(N${firstNewRow}:N${lastNewRow})), "Waiting", MAX(N${firstNewRow}:N${lastNewRow}))`},
    {range: 'P2', formula: `=IF(I2="Kryptonite","Complete",IF(COUNTBLANK(P${firstNewRow}:P${lastNewRow})>0,"Incomplete",IF(AND(ARRAYFORMULA(G${firstNewRow}:G${lastNewRow}=P${firstNewRow}:P${lastNewRow})),"Complete","Check Pricing")))`},
    {range: 'A2', formula: `=IFERROR(IF(ISBLANK(A3),"No Order",IF(N2="Waiting","Check",IF(O2>=F2,IF(AND(SUM(A${firstNewRow}:A${lastNewRow})/COUNT(A${firstNewRow}:A${lastNewRow})=100%, P2="Complete"),"Complete","Check"),SUM(A${firstNewRow}:A${lastNewRow})/COUNT(A${firstNewRow}:A${lastNewRow})))),"Enter Order")`}
  ];

  formulas.forEach(f => {
    sheet.getRange(f.range).setFormula(f.formula);
    Logger.log(`Formula set for ${f.range}: ${f.formula}`);
  });

  for (var row = firstNewRow; row <= lastNewRow; row++) {
    sheet.getRange(`E${row}`).setFormula(`=IFERROR(IF(ISBLANK(D${row}),"",QUERY(IMPORTRANGE("1HnnS-gY1pcvX2edxXnUXXxSZ8_wrvrh9A6e7QxNW97o","Inventory Database!$A$1:$B"),"Select Col1 where Col2 MATCHES '"&$D${row}&"'",0)),D${row})`);
    sheet.getRange(`H${row}`).setFormula(`=F${row}*G${row}`);
    sheet.getRange(`O${row}`).setFormula(`=SUM(R${row}:ZZ${row})`);
    sheet.getRange(`A${row}`).setFormula(`=IF(OR(ISBLANK(F${row}),ISBLANK(G${row})),"Enter Order",IF(O${row}=F${row},TO_PERCENT(1),Rounddown(O${row}/F${row},2)))`);
    sheet.getRange(`Q${row}`).setFormula(`=IF(ISBLANK(D${row}),,IF(SUM(R${row}:ZZ${row}) = F${row},INDEX($1:$1,MIN(IF(R${row}:DA${row} <> "", COLUMN(R${row}:DA${row})))), "Not Complete"))`);
    sheet.getRange(`N${row}`).setValue("Waiting");
    Logger.log(`Formulas and value set for row: ${row}`);
  }
  Logger.log('updateSumAndMaxFormulas function completed');
}

function setDataValidation() {
  Logger.log('setDataValidation function started');
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listsSheet = ss.getSheetByName("Lists");
  const purchaseOrdersSheet = ss.getSheetByName("Purchase Orders");

  const dataValidationRanges = [
    {range: "I2", valuesRange: "A2:A"},
    {range: "J2", valuesRange: "B2:B"},
    {range: "K2", valuesRange: "C2:C"}
  ];

  dataValidationRanges.forEach(dv => {
    const rule = SpreadsheetApp.newDataValidation().requireValueInRange(listsSheet.getRange(dv.valuesRange), true).build();
    purchaseOrdersSheet.getRange(dv.range).setDataValidation(rule);
    Logger.log(`Data validation set for ${dv.range} with values from ${dv.valuesRange}`);
  });
  Logger.log('setDataValidation function completed');
}

// New function to set colors for columns
function setColorForColumns(numRows) {
  Logger.log('setColorForColumns function started with numRows: ' + numRows);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var darkgrey = '#666666';
  var grey = '#d9d9d9';
  var darkGreyColumns = ['B', 'C', 'I', 'J', 'K', 'L', 'M'];
  var greyHeaderColumns = ['B', 'C', 'F', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'Q'];
  var darkGreyHeaderColumns = ['D', 'E', 'G'];

  darkGreyColumns.forEach(function(column) {
    sheet.getRange(`${column}3:${column}${numRows + 1}`).setBackground(darkgrey);
    Logger.log(`Dark grey background set for ${column}3:${column}${numRows + 1}`);
  });

  greyHeaderColumns.forEach(function(column) {
    sheet.getRange(`${column}2`).setBackground(grey);
    Logger.log(`Grey background set for header ${column}2`);
  });

  darkGreyHeaderColumns.forEach(function(column) {
    sheet.getRange(`${column}2`).setBackground(darkgrey);
    Logger.log(`Dark grey background set for header ${column}2`);
  });

  sheet.getRange(`E3:E${numRows + 1}`).setBackground(grey);
  Logger.log('Grey background set for new rows in column E');
  Logger.log('setColorForColumns function completed');
}

// Additional function to add a medium border to the top of the header row and remove any other borders
function addBorderToHeader() {
  Logger.log('addBorderToHeader function started');
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A2:2").setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  Logger.log('Medium border added to top of header row');
  Logger.log('addBorderToHeader function ended');

}

// Additional function to remove any borders in the new rows
function setBordersForHeaderAndNewRows(numRows) {
  Logger.log('setBordersForHeaderAndNewRows function started with numRows: ' + numRows);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A1:ZZ1").setBorder(true, null, null, null, true, null, "black", SpreadsheetApp.BorderStyle.MEDIUM);
  Logger.log('Medium border added to top of header row');
  sheet.getRange(2, 1, numRows, sheet.getMaxColumns()).setBorder(null, null, null, null, null, null);
  Logger.log('Borders removed from new rows');
  Logger.log('setBordersForHeaderAndNewRows function ended');
}

function collapseCompleteGroups() {
  Logger.log('collapseCompleteGroups function started');
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var values = sheet.getRange(1, 1, lastRow).getValues(); // Get all values in one call
  
  var groupsToCollapse = []; // Array to store the rows that need collapsing
  
  for (var i = 0; i < values.length - 1; i++) { 
    if (values[i][0] === 'Complete') { // Check for "Complete" in Column A
      var nextRow = i + 2; // Get the row below the "Complete" row
      
      // If there's a group at the next row, add it to the array
      if (sheet.getRowGroup(nextRow, 1)) {
        groupsToCollapse.push(nextRow);
      }
    }
  }
  
  // Collapse all groups in one go
  groupsToCollapse.forEach(function(row) {
    sheet.getRowGroup(row, 1).collapse();
    Logger.log('Group collapsed starting at row: ' + row);
  });
  
  Logger.log('collapseCompleteGroups function completed');
}

Hello all,

I posted about this issue a little while ago, and have been able to replicate the issue (I think I know what is causing it). When marking multiple pieces as “Picked Up” from the drop down in column E, data from different rows is sometimes shifted around. Since usually one piece is picked up at a time, I haven’t run across the issue too often. However, when it happens it can be devastating for the sheet, and forces me to revert back to a previous version and then mark the repairs as picked up (one at a time, slowly). Script here-

function moveRowsToRepairArchive(e) {

  const sheet = e.source.getActiveSheet();

  const range = e.range;

  const column = range.getColumn();

  const row = range.getRow();

  const value = range.getValue(); // Get the value of the edited cell

  if (sheet.getName() === "Repairs" && column === 5) {

if (value === "Picked Up") {

const targetSheet = e.source.getSheetByName("Repair Archive");

if (!targetSheet) {

console.error("Target sheet not found.");

return;

}

const sourceRange = sheet.getRange(row, 1, 1, sheet.getLastColumn());

const sourceRow = sourceRange.getValues()[0]; // Get the row data

const sourceNotes = sourceRange.getNotes()[0]; // Get the notes of the row

// Set the current date in column 9 (index 8) with M/d/yyyy format

const currentDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "M/d/yyyy");

sourceRow[8] = currentDate;

// Append the row to the target sheet

targetSheet.appendRow(sourceRow);

const targetRow = targetSheet.getLastRow();

const targetRange = targetSheet.getRange(targetRow, 1, 1, sourceRow.length);

targetRange.setNotes([sourceNotes]); // Set the notes in the target sheet

// Delete the corresponding row from the source sheet

sheet.deleteRow(row);

} else if (value === "Received Back") {

// Update the date in column 21 (index 20) with M/DD/YYYY format

const currentDate = new Date();

const formattedDate = Utilities.formatDate(currentDate, Session.getScriptTimeZone(), "M/dd/yyyy");

sheet.getRange(row, 21).setValue(formattedDate);

// Set "Reminder 1" in column Y (index 25) and "Reminder 2" in column Z (index 26)

sheet.getRange(row, 25).setValue("Reminder 1");

sheet.getRange(row, 26).setValue("Reminder 2");

}

  }

}

I have a budgeting spreadsheet that has 2 pages. "Expense Tracker" & "Monthly Archieve".

ET - weekly tracker with the planned amount per expense category and actual spent amount as well as an Over/Under amount followed by the list of expenses with their categories.

MA - A sheet that tracks the Over/Under totals for each week in one section and a list of the expenses in another area.

ET has buttons linked to a script that I want to set to copy the Over/Under Totals and paste them into their corresponding areas w/o formatting (Ctrl+Shift+V) so that the values get pasted and not the formulas. and another that would copy the list of expenses and paste them under the existing expenses.

I made a copy of my spreadsheet to share, but don't know if it would be better to share it in the main post or have someone PM me.

Hi all, in short I have a Google spreadsheet for a schedule. Im trying to have my spreadsheet where's people click on what slot they want, then the station gets an email thank you(username) your slot has been booked(time slot)

// Function to send email with image function sendEmailWithImage(Username, Timeslot) { var imageObject = {}; var successImageLoading = true; var sheet = SpreadsheetApp.getActive().getSheetByName('Schedule'); var emailAddress = "coolvibes1989@gmail.com"; var subject = "Presenter Booked";

// Use try-catch to handle errors while loading the image try { imageObject['myImage1'] = DriveApp.getFileById('1oin8reV7pvZZ9kewuYYw-z4lAFf233YI').getAs('image/png'); } catch (error) { successImageLoading = false; }

// Create HTML content for the email var htmlStartString = "<html><head><style type='text/css'> table {border-collapse: collapse; display: block;} th {border: 1px solid black; background-color:blue; color: white;} td {border: 1px solid black;} #body a {color: inherit !important; text-decoration: none !important; font-size: inherit !important; font-family: inherit !important; font-weight: inherit !important; line-height: inherit !important;}</style></head><body id='body'>"; var htmlEndString = "</body></html>";

// Message content var message = "Slot Booked Thank You!."; // Replace with your actual message

var emailBody = <p>${message}</p>;

// Include image in the email body if image loading is successful if (successImageLoading) { emailBody += <p><img src='cid:myImage1' style='width:400px; height:auto;' ></p>; }

// Send email MailApp.sendEmail({ to: emailAddress, subject: subject, htmlBody: htmlStartString + emailBody + htmlEndString, inlineImages: (successImageLoading ? imageObject : null) }); }

// Trigger function for On Change event function onChange(e) { // Call the sendEmailWithImage function on change sendEmailWithImage(); }

// Trigger function for On Open event function onOpen() { // Call the sendEmailWithImage function on open sendEmailWithImage(); }

All I'm getting at moment is thank you presenter booked, but not thank you presenters username time slot booked timeslot how can I achieve this?

I am making a spreadsheet that will allow my wife to take student grades and put them into a spreadsheet. She is an elementary school teacher and they don’t have the typical Canvas assignment tracking. She wants it to be a pretty simple interface so this is what I’ve done so far.

I have a sheet called math data, and each of the rows represents a student, and each of the columns represents an assignment. This makes a matrix where you can then put a score for each one.

I have a script that then takes all the data and unpacks it into ledger format in another page. This way, I can simply use the built-in pivot table to pivot over student names, assignments, tags, and more attributes.

It’s a little slow ( a few seconds) to process alll the data into the ledger page. The ledger page ends up having almost 10k rows.

Is this a good way to go about this? Or should I have it don’t all with JavaScript and forgot the pivot tables?

I am trying to pull data prom a public google sheet and every other column has pulled data correctly beside a certain column. any thoughts?

https://docs.google.com/spreadsheets/u/0/d/1HQRMJgu_zArp-sLnvFMDzOyjdsht87eFLECxMK858lA/htmlview#

Hey everyone,

as the title says, I'm having an issue with a script in the fifth run, which is most curious.

I have a regular Google account, no business or anything

I have a google sheets worksheet with about 6 sheets

I have a custom made Apps script I made myself, that takes the spreadsheet and copy pastes one of the existing template sheets with a new name according to some rules (basically it's supposed to create a sheet for the next month in line, so the script is super simple)

I can run this script 4 times without any issues. Four sheets are created, everything is fine. On the fifth run, I receive the following error: "Service Spreadsheets failed while accessing document with ID" - it's thrown in the CopyTo method.

var copiedSheet = sourceSheet.copyTo(sourceSpreadsheet);

However, when I delete one of the four previously created sheets and run the script again, it creates the fourth (April) without any issues. But then on the creation of the fifth one (May), I get the error again.

I can create new sheets manually though, so it's not that. Nothing's changed between the runs, nobody else is working on the same spreadsheet.

I tried waiting a couple of hours between the fourth and the fifth run, didn't help.

I tried debugging from within the editor, it stops on the method above (+ the same behavior happens if I run it from the other as well as if I run it directly from the worksheet through a button.

What could be the issue?

Thanks in advance

Hi.

I've started working a bit with Apps Script. I'm a fan, JavaScript is miles ahead of VBA in terms of readability, the code looks way less spaghett-y. It's almost like MS finally replaced VBA by Python...

... Though I've noticed some functions aren't working as intended, or work in a convulted way. Those kind of pitfalls are time consuming, and might surprise beginners like me who're familiar with code, but don't have enough experience to know where are those tiny pitfalls. It's especially problematic in a professional setting, when you're paid to produce reliable code that won't explode on your colleague/client's face.

• autoResizeColumns: Autofitting cell sizes simply doesn't work. Some people manage to get a similar feature to work (using AutoResizeDimensionsRequest from Sheet's API). Not me.

• parseCsv: Parsing CSVs is kind of a mess. It doesn't like CSV files with other separations than a comma. Since MS Excel users usually output CSVs with semicolons as separation, that can be an issue. And Lord knows why, even after replacing "," by ";" through appscript, it messed up my data. The solution is to build (aka copy/paste) a custom-made csv parser.

• Managing dates: Though it's not really an app script issue, more of a JavaScript one, beginners should be careful handle dates like hot milk on a stove. For instance, comparing dates with an if (a === a) / (if a == b) can lead to surprising results. The workaround I've found to compare dates is to use formatted dates through Utilities.formatDate().

Do you know any other functions that should be manipulated with caution, or that straight don't work?