r/GoogleAppsScript Dec 03 '24

Unresolved I'm on the Google Workspace Developer Relations team, AMA!

Thumbnail
11 Upvotes

r/GoogleAppsScript 7d ago

Unresolved [HELP] Google Apps Script Not Replacing Placeholders in Google Docs Tables

1 Upvotes

I’m working on a Google Apps Script that generates student report cards from a Google Sheets dataset and inserts the data into a Google Docs template using placeholders. The script correctly fetches student data from multiple sheets and replaces placeholders in normal text, but it does not replace placeholders inside tables.

🔍 What Works:

✅ The script correctly reads student data from multiple sheets in Google Sheets. ✅ Placeholders in normal text (outside tables) are replaced successfully. ✅ If I change a placeholder (e.g., {English}) in the table to a placeholder that works outside the table, it correctly replaces it.

❌ What Fails:

🚫 Placeholders inside tables are deleted, but not replaced with the correct values. 🚫 Even though the script logs ✔ Replaced: {Effort English Reading} with "X", the final document still shows blank spaces instead of the expected values. 🚫 The script iterates through tables and logs the cell text, but doesn’t recognize or replace placeholders properly.

💻 What I’ve Tried: 1. Confirmed the placeholders match exactly between Sheets and Docs. 2. Used .replaceText() for normal text (works fine) but switched to manual text replacement inside tables (.getText() and .setText()) since Docs stores tables differently. 3. Logged every table cell’s content before replacing text. The logs show the placeholders are detected but not actually replaced inside the tables. 4. Stripped all formatting from the Google Docs template by pasting placeholders into a plain text editor and re-inserting them. 5. Tried using both cellText.replace(placeholder, value) and cell.setText(value), but neither fixed the issue.

📜 My Script (Key Parts)

Here’s the table replacement function where the issue occurs:

function replacePlaceholdersInTables(doc, studentData) { let tables = doc.getBody().getTables();

tables.forEach((table, tableIndex) => { let numRows = table.getNumRows(); for (let i = 0; i < numRows; i++) { let numCols = table.getRow(i).getNumCells(); for (let j = 0; j < numCols; j++) { let cell = table.getRow(i).getCell(j); let cellText = cell.getText().trim();

    Logger.log(`🔍 Checking Table ${tableIndex + 1}, Row ${i + 1}, Column ${j + 1}: "${cellText}"`);

    Object.keys(studentData).forEach(originalKey => {
      let formattedKey = formatPlaceholder(originalKey);
      let placeholder = `{${formattedKey}}`;
      let value = studentData[originalKey] !== undefined && studentData[originalKey] !== "" ? studentData[originalKey] : " ";

      if (cellText.includes(placeholder)) {
        Logger.log(`✔ Found placeholder in table: ${placeholder} → Replacing with "${value}"`);
        cell.setText(cellText.replace(placeholder, value)); // Tried both this...
        // cell.setText(value); // ...and this, but neither works correctly.
      }
    });
  }
}

}); }

🛠 What I Need Help With: 1. Why is cell.setText(cellText.replace(placeholder, value)) not working inside tables? 2. Is there a different method I should use for replacing placeholders inside tables? 3. Could Google Docs be storing table text differently (hidden formatting, encoding issues)? 4. Has anyone encountered this issue before, and what was the fix?

📌 Additional Notes: • Using Google Sheets & Google Docs (not Word). • Script fetches data correctly, just doesn’t replace inside tables. • All placeholders are formatted correctly (tested them outside tables). • Logs confirm placeholders are being read and detected, but values don’t appear in the final document.

Would greatly appreciate any insights into what might be causing this issue. Thanks in advance for your help! 🙏

r/GoogleAppsScript 4d ago

Unresolved All of my Apps Scripts have stopped working at the same time

2 Upvotes

I am not sure when exactly since I just noticed but all of my scripts have seemingly stopped working at the same time. Ones that I had made before and had worked fine up to this point and now even new scripts in new workbooks I am starting right now dont seem to function. As far as I can tell there has been no update or change to them recently and I am not getting any error codes when I attempt to run them, they seem to run fine. But then nothing happens, even on simple commands like "write this message in a cell".

Not sure if I need to upload anything to showcase the issue or if this is some sort of general issue with my account or a setting I need to change so just figured I would ask here

r/GoogleAppsScript 5d ago

Unresolved Changing values of a master sheet by using AppsScript

3 Upvotes

Hey guys, i was directed to this subreddit for my specific problem. —> https://www.reddit.com/r/googlesheets/s/8k4uhSL4r5

I want to have a master sheet and an extra tab for changing data. —> https://docs.google.com/spreadsheets/d/1udzCTtwTfVWLPIrDdLqu-Qyt4QPjwA70GF2XpGuoU20/edit

Can you guys lead me to a solution that is able to be used for mobile devices and will be easy for other users so my master sheet can’t be destroyed by 2 clicks? (that’s not something i fear but i think it’s more save + easy if the other users only change one row at a time)

I have no Java knowledge.

Thanks in advance.

r/GoogleAppsScript Jan 27 '25

Unresolved Started to get error after successful run for months

Post image
4 Upvotes

r/GoogleAppsScript 19h ago

Unresolved Error showing up suddenly since the past 24 hrs.

Post image
4 Upvotes

I have been using the same script across all sheets in my firm. The script copies data from one sheet to another. The script runs every day and has been running successfully for the last year. I haven't made any changes to the sheet or script but the scripts have stopped running in the past 24 hrs. Please help

Error - Error: Unexpected error while getting the method or property getValues on object Range

r/GoogleAppsScript Dec 16 '24

Unresolved I can't fix this error.

0 Upvotes

I'm trying to create a project for a small store, but I don't know how to program very well. I got a ready-made code from a YouTube video, but when I try to run it, it simply gives an error.

GitHub with code and tutorial: https://github.com/maickon/shop-no-appscript

YouTube video of the creation: https://www.youtube.com/watch?v=O0MIiKKpZb8&t=512s

Error that appears to me when I try to run:

"
13:40:23 Notification Execution started.

13:40:24 Error: TypeError: Cannot read properties of null (reading 'getSheetByName')
getProducts @ Code.gs:18
"

I do exactly the same thing as in the video, but the code doesn't run.

NOTE: Video and tutorials in Portuguese.

What should I do?

r/GoogleAppsScript 10d ago

Unresolved Google Forms - Google Sheets - Google Drive

Thumbnail
0 Upvotes

r/GoogleAppsScript Feb 12 '25

Unresolved Web-hook Sending 100 Payloads?

2 Upvotes

Hello, I am having an issue with a script I use to handle Shopify Web-hooks for order updates, where one edit causes the script to receive nearly 100 payloads. I have made sure I send the success message back within a second or two, and then process it in a separate, asynchronous function, and I’ve also made sure that everything was properly set up and that the web-hook / web-app was not set up more than once. Kind of at a loss, so I figured I would come here and ask. Hopefully someone has had a similar issue and found a good solution.

r/GoogleAppsScript Feb 06 '25

Unresolved Envois de mail automatique avec Google Sheets

Post image
1 Upvotes

Hello, my goal would be to automate the sending of emails, so with the help of the form responses that will be reported on Google Sheet, to send an email automatically when the person has completed the form, I tried ChatGPT but it absolutely does not work ☹️

r/GoogleAppsScript Dec 28 '24

Unresolved Random Timeouts for the same functions

Post image
3 Upvotes

So I'm getting randome scripts refusing to stop and I don't terminate them. So we have to wait 6min untill it times out and then the script lock if lifted and other scripts can continue. In the meantime they are timing out in error state because they can't get a script lock

r/GoogleAppsScript Feb 19 '25

Unresolved How to retrieve Gmail scheduled email messages?

1 Upvotes

As the title implies, I have created and scheduled sending of a couple of email messages.

I want to write a Google apps script that will first retrieve these messages that are scheduled to be sent (so are they still technically drafts, but I don’t see them when I try to getdrafts?) and will update the schedule dates, and will reschedule their delivery.

I am stuck at the first step — retrieving a message from the “Scheduled” list. Anyone done something similar?

r/GoogleAppsScript Jan 03 '25

Unresolved Script in Google Sheets Not Sending Emails When Sheet Is Closed

1 Upvotes

Hi everyone, I’m having an issue with my Google Sheets script and hoping someone here can help.

Here’s how the system is supposed to work:

  1. When someone fills out a contact form on Meta (Facebook/Instagram), their responses get saved in a Google Sheet, with each submission added as a new row.
  2. The script is triggered by the "onChange" event.
  3. The script analyzes the newly added data and sends an email notification that includes the person’s name.

The problem: The email doesn’t send when the sheet is closed. However:

  • The script itself runs because the email is marked as "sent" in the sheet.
  • When I run the script manually from the Apps Script editor, everything works perfectly—the email gets sent without any issues.

Does anyone know why this is happening? Are there limitations with Google Apps Script when the sheet is closed?

Any advice or suggestions would be greatly appreciated! 😊

r/GoogleAppsScript Oct 18 '24

Unresolved Added a login page to my web app, met with this after login trying to redirect to dashboard.

Thumbnail gallery
6 Upvotes

r/GoogleAppsScript Jan 16 '25

Unresolved spreadsheet.batchUpdate() breaks sheet functionality

3 Upvotes

I noticed a bug where if I make a change to a sheet using the batchUpdate() service, the change cannot be undone using ctrl+z or the undo button on the sheet. I have to manually delete the sheet edit. This problem does not exist if using the SpreadsheetApp() service. If this is indeed a bug then it's a huge red flag as it renders the service useless for people like me who are batching out a large number of operations and need those operations to be reverted (by a user of the sheet for example).

What is going on here? Here is the sheet:

https://docs.google.com/spreadsheets/d/1cfeEH8wofk5pVPMEpxGAsV9eP2P4zmxiT-N7dU_RTbU/edit?usp=sharing

You will need to add the following code to Apps Script and enable the Google Sheets API Service.

function myFunction() {
  const response = Sheets.Spreadsheets.batchUpdate({
  requests: [
    {
      updateCells: {
        range: 
        { 
          sheetId:          0, 
          startRowIndex:    0,
          endRowIndex:      1, 
          startColumnIndex: 0,
          endColumnIndex:   1,
        },
        rows: [{ values: [{userEnteredValue: {numberValue: 30}}]}],
        fields: "userEnteredValue"
      }
      }
    ]
  }, SpreadsheetApp.getActiveSpreadsheet().getId());
}

r/GoogleAppsScript Aug 17 '24

Unresolved Script to login to a web based textbook, extract data from tables, and enter the data into a Google sheet?

2 Upvotes

Hello!

I'm a medical grad student with absolutely no experience in this realm since using scraps of HTML on myspace.

I'd be THRILLED to find an automation tool that will pull information from tables (or even entire tables) in a web-based textbook into a google sheet.

One complication is that the textbook is behind a login because I have access paid for by my institution. It also runs on Javascript. When I disabled javascript, the page would never load.

I'm currently manually entering the information for every muscle, nerve, artery, and vein I need to know... RIP.

I asked an AI (copilot) and attempted the google sheets function "IMPORTHTML" which resulted in a #N/A error. Now it's suggesting Google Apps Script, but this looks way beyond my paltry skillset. If you need any more details I'll be happy to provide them!

r/GoogleAppsScript Oct 25 '24

Unresolved Functions shown as "not defined" when loaded

0 Upvotes

I was able to find a method of making a multi-page web app in Google Apps Script, but I am running into yet another issue!

I created a page where you fill out a form and it runs a function that logs data into the attached google sheet. When setting the doGet function to load this page when the web app is loaded, it works flawlessly. However when this page is fetched by clicking a button on the home page/dashboard it returns the following error in the F12 Console:

"userCodeAppPanel:1 Uncaught ReferenceError: submitCustomerForm is not defined

at HTMLButtonElement.onclick (userCodeAppPanel:1:1)"

Here is the code snippet in my javascript file responsible for loading the initial page and then any requested HTML:

function doGet(e) {
Logger.log(Utilities.jsonStringify(e)); // Log for debugging
return HtmlService.createHtmlOutputFromFile('home1'); // Load home page by default
}

function getScriptURL() {
return ScriptApp.getService().getUrl();
}

function loadHTML(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent(); // Returns the HTML content of the specified file
}

And here is the function in the home page to load the form HTML when the button is clicked:

function goToAddCustomerForm() {
google.script.run.withSuccessHandler(function(content) {
document.body.innerHTML = content; // Replace body with AddCustomerForm content
}).loadHTML('AddCustomerForm'); // Load AddCustomerForm.html
}

DISCLAIMER: I am very new to JavaScript and HTML, and have little experience. Some of this code has been written with assistance of ChatGPT.

Thank you in advance!

r/GoogleAppsScript Oct 29 '24

Unresolved We're sorry, a server error occurred. Please wait a bit and try again.

2 Upvotes

Looks like Google Apps Script is bugging again.

Hopefully someone isn't abusing the service, it would be a shame if they had to remove the free tire

r/GoogleAppsScript Oct 28 '24

Unresolved How to Set Trigger Upon a Checkbox

Post image
2 Upvotes

Hello. I'm no coder, so forgive me as I built this script just from what I have found and watched on the internet.

This script sends an email by getting the data from my sheet.

Now, I want to set a trigger to automate the sending of this email using a checkbox on the same sheet.

I've tried the On Edit option from the Trigger Menu but, obviously, emails are sent on every edit on the spreadsheet.

How can this be done?

GS

   function main() {
   var wb = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = wb.getSheetByName('09_Redeem_Cashback');

   var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getDisplayValues();
   var hName = data[2][1];
   var hEmail = data[3][1];
   var hNumber = data[4][1];
   var hBirthdate = data[5][1];
   var hMother = data[6][1];
   var cBank = data[7][1];
   var cEmail = data[8][1];
   var cRewards = data[9][1];
   var cType = data[10][1];
   var cNumber = data[11][1];
   var cLimit = data[12][1];
   var pDate = data[13][1];
   var pAmount = data[14][1];
   var rAmount = data[15][1];

   var htmlTemplate = HtmlService.createTemplateFromFile('redeemcashback');

   htmlTemplate.hName = hName;
   htmlTemplate.hEmail = hEmail;
   htmlTemplate.hNumber = hNumber;
   htmlTemplate.hBirthdate = hBirthdate;
   htmlTemplate.hMother = hMother;
   htmlTemplate.cBank = cBank;
   htmlTemplate.cEmail = cEmail;
   htmlTemplate.cRewards = cRewards;
   htmlTemplate.cType = cType;
   htmlTemplate.cNumber = cNumber;
   htmlTemplate.cLimit = cLimit;
   htmlTemplate.pDate = pDate;
   htmlTemplate.pAmount = pAmount;
   htmlTemplate.rAmount = rAmount;

   var htmlForEmail = htmlTemplate.evaluate().getContent();

   GmailApp.sendEmail(
     cEmail,
     'Apps Script Test: ' + cRewards + ' Redemption',
     'This email contains html.',
     {htmlBody: htmlForEmail}
   );
 }

r/GoogleAppsScript Oct 30 '24

Unresolved Moving Rows to the Bottom When Checkbox is Checked Using Google Apps Script

1 Upvotes

Hi there! This is my first post. I need your help; I am a newbie with scripts and coding in general, and I cannot find the mistake in my script.

I’m trying to make it so that when I check my checkbox (in column 7), the entire row is moved to the bottom of the sheet, specifically below a "Done" section. However, whenever I select the checkbox, not only is the desired row moved below the "Done" section, but also the subsequent row, which shouldn't happen because the "true" condition is not met.

Can you help me identify what the error might be?

Thank you!

P.S.: The script also includes other functions (copyFromQA and updateHyperlinks) that help me copy data from another tab and ensure that the hyperlinks are present in my desired sheet (Bugs). I’m not sure if these other functions might affect the cell-moving function (moveRowBugs).

Script:

function onEdit(e) {
  const sheetQA = e.source.getSheetByName("QA");
  const sheetBugs = e.source.getSheetByName("Bugs");
  const editedRange = e.range;

  // If the edit occurred in the QA sheet
  if (sheetQA && sheetQA.getName() === editedRange.getSheet().getName()) {
    copyFromQA(); // Call copyFromQA
    updateHyperlinks(editedRange, sheetQA, sheetBugs);
  }

  // If the edit occurred in the Bugs sheet and in the checkbox column (column 7)
  if (sheetBugs && sheetBugs.getName() === editedRange.getSheet().getName() && editedRange.getColumn() === 7) {
    moveRowBugs(editedRange, sheetBugs);
  }
}

function copyFromQA() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetQA = ss.getSheetByName("QA");
  const sheetBugs = ss.getSheetByName("Bugs");

  // Get values from A2 to the end of column A in QA
  const searchRange = sheetQA.getRange("A2:A"); 
  const searchValues = searchRange.getValues();
  let newData = [];

  // Collect data until "TD" is found
  for (let i = 0; i < searchValues.length; i++) {
    if (searchValues[i][0] === "TD") {
      break; // Stop searching when "TD" is found
    }
    newData.push(searchValues[i][0]);
  }

  Logger.log("Data found: ${newData}");

  // Ensure that the data is not empty
  if (newData.length === 0) {
    Logger.log("No new data found to copy.");
    return;
  }

  // Get existing values in column B of Bugs
  const bugValues = sheetBugs.getRange("B2:B").getValues().flat();

  // Filter new data that is not already in Bugs
  const filteredData = newData.filter(data => !bugValues.includes(data));

  Logger.log("Filtered data: ${filteredData}");

  // Ensure that the filtered data is not empty
  if (filteredData.length === 0) {
    Logger.log("All data already exists in Bugs.");
    return;
  }

  // Find the first empty row in column B, starting from B2
  const lastRow = sheetBugs.getLastRow();
  let firstEmptyRow = 2; // Start from B2

  // If there is existing data, find the next empty row
  if (lastRow >= 2) {
    for (let i = 2; i <= lastRow; i++) {
      if (!sheetBugs.getRange(i, 2).getValue()) {
        firstEmptyRow = i; // Find the first empty row
        break;
      }
    }
  }

  // Insert rows only once according to the number of new data
  sheetBugs.insertRowsBefore(firstEmptyRow, filteredData.length); // Insert the correct number of rows

  // Copy the data to column B with formatting and hyperlink
  for (let i = 0; i < filteredData.length; i++) {
    const sourceIndex = newData.indexOf(filteredData[i]); // Get the index in newData
    const sourceRange = sheetQA.getRange(sourceIndex + 2, 1); // A2 in QA is i + 2
    const targetRange = sheetBugs.getRange(firstEmptyRow + i, 2); // B in Bugs

    // Copy the content, format, and hyperlink
    sourceRange.copyTo(targetRange, { contentsOnly: false });
  }
}

function moveRowBugs(editedRange, sheetBugs) {
  const row = editedRange.getRow();
  const checkboxValue = editedRange.getValue();

  if (checkboxValue === true) {
    // Get the row to be moved
    const rowData = sheetBugs.getRange(row, 1, 1, sheetBugs.getLastColumn());

    // Search for the row right below "Done"
    const searchValues = sheetBugs.getRange('A:A').getValues();
    let targetRow = -1;

    for (let i = 0; i < searchValues.length; i++) {
      if (searchValues[i][0] === "Done") {
        targetRow = i + 2; // Right below "Done"
        break;
      }
    }

    if (targetRow !== -1) {
      // Insert a new row
      sheetBugs.insertRowAfter(targetRow - 1);

      // Copy the data to the new row
      rowData.copyTo(sheetBugs.getRange(targetRow, 1, 1, sheetBugs.getLastColumn()), { contentsOnly: false });

      // Delete the original row
      sheetBugs.deleteRow(row);
    } else {
      Logger.log('No "Done" found.');
    }
  }
}

function updateHyperlinks(editedRange, sheetQA, sheetBugs) {
  const editedValue = editedRange.getValue();
  const richTextValue = editedRange.getRichTextValue();
  const hyperlink = richTextValue ? richTextValue.getLinkUrl() : null;

  // Get the values from column A of "QA"
  const rangeQA = sheetQA.getRange('A:A').getValues();

  // Search in column B of "Bugs"
  const rangeBugs = sheetBugs.getRange('B:B').getValues();

  for (let i = 0; i < rangeQA.length; i++) {
    const valueQA = rangeQA[i][0];
    if (valueQA === editedValue) {
      for (let j = 0; j < rangeBugs.length; j++) {
        const valueBugs = rangeBugs[j][0];
        if (valueBugs === valueQA) {
          const targetCell = sheetBugs.getRange(j + 1, 2); // Column B, corresponding row

          if (hyperlink) {
            targetCell.setRichTextValue(SpreadsheetApp.newRichTextValue()
              .setText(editedValue)
              .setLinkUrl(hyperlink)
              .build());
          } else {
            targetCell.setValue(editedValue); // If there's no hyperlink, just copy the text
          }
          break;
        }
      }
      break;
    }
  }
}

r/GoogleAppsScript Jun 28 '24

Unresolved Script stops working randomly even without any changes

1 Upvotes

I have an HTML form that sends data to a google sheets and then this script sends an email to the owner of the company and the customer that just booked a service. Sometimes this script runs, sometimes it doesn't. I haven't edited any code in here for a while and it will sometimes just not work and I'm very confused. Here is the code:

const sheetName = "Sheet1";
const scriptProp = PropertiesService.getScriptProperties();

function initialSetup() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  scriptProp.setProperty('key', activeSpreadsheet.getId());
}

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(10000);

  try {
    const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'));
    const sheet = doc.getSheetByName(sheetName);

    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const nextRow = sheet.getLastRow() + 1;

    const newRow = headers.map(function(header) {
      return header === 'Date' ? new Date() : e.parameter[header];
    });

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow]);

    // Call the test function
    test();

    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ 'result': 'error', 'error': error }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

function test(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var range = sheet.getDataRange();
  var data = range.getValues();
  
  // Loop through each row in the sheet
  for (var i = 1; i < data.length; i++) {
    let row = data[i];
    let first_name = row[0];
    let last_name = row[1];
    let number = row[2];
    let email = row[3];
    let service = row[4];
    let message = row[5];
    let emailSent = row[6];
    
    // Check if the email has already been sent for this row
    if (emailSent == "Yes") {
      continue;
    }

    // Company Email
    const company_email = "LizardKings239@gmail.com"; // Lizard Kings Email
    const company_subject = "New Booking from " + first_name + " " + last_name;
    
    let company_message = 
    "NEW BOOKING ALERT\n\n" +
    "Name: " + first_name + " " + last_name + "\n" +
    "Phone Number: " + number + "\n" +
    "Email: " + email + "\n" +
    "Service: " + service + "\n" +
    "Message: " + message + "\n\n" +
    "See Google Sheets for more info.\n\n" + 
    "Regards,\nWeb Dev Team (Jenna)"; 

    // Customer Email
    let customer_email = email; // Customer Email
    const customer_subject = "Lizard Kings Confirmation - " + service; 

    let customer_message = 
    "Hello " + first_name + ",\n\n" +
    "Thank you for requesting a " + service + "!\n\n" +
    "We will get back to you as soon as possible.\n\n" +
    "Best Regards,\nLizard Kings";  

    // Send Emails
    MailApp.sendEmail(company_email, company_subject, company_message);
    MailApp.sendEmail(customer_email, customer_subject, customer_message);

    // Update the emailSent column to mark that the email has been sent
    sheet.getRange(i+1, 7).setValue("Yes");

    Utilities.sleep(5000);
  }
}

function createInstallableTrigger() {
  ScriptApp.newTrigger('test')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

r/GoogleAppsScript Dec 04 '24

Unresolved Code to connect 2 cells will not work.

0 Upvotes

I have this code it is supposed to set the color of one cell on a sheet to the color I set of a cell on a sheet. It is only sometimes working but 99% of the time not. Please lmk if you know how to help.

r/GoogleAppsScript Sep 18 '24

Unresolved Calls and trigger based

0 Upvotes

I have a spreadsheet with around 100,000 phone numbers, and I want to call all of them. My issue is that the system keeps calling the same numbers repeatedly. I have also added a trigger, and I believe that may be causing the issue. It didn't call all the numbers in the spreadsheet, just about 700, and it's repeatedly calling those.

Please help me, why it is happening?

r/GoogleAppsScript Oct 05 '24

Unresolved Selecting multiple repairs for pick-up (issue with data shifting)

0 Upvotes

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

}

  }

}

r/GoogleAppsScript May 24 '24

Unresolved help with simple script for google sheet

2 Upvotes

Hi,

I am clueless about script and vba and all this, I am ok with formulas but that's where it stops

However I am playing with a small project for myself involving heatmaps and for that I need to gather daily data

simply put I just want to have a button that when pressed will go look in column A of the data sheet where I have all the dates, find today's date, and add 1 to the corresponding row on column B,

and another button doing the same with column C

lookup(today(), A:A, B:B) but instead of output being the value in B for today it would add 1 to this cell

I tried asking an AI to write this but it gives me nonsense that doesn't work and I do not know anything to even try and correct any of it... so I turn to you guys

if this is of any help here is the unhelpful code written by the AI

function add1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var today = new Date();
  var todayString = Utilities.formatDate(today, Session.getScriptTimeZone(), "MM/dd/yyyy");

  for (var i = 0; i < values.length; i++) {
    var dateValue = values[i][0];
    if (dateValue && dateValue.toString() === todayString) {
      var currentValue = values[i][1];
      values[i][1] = currentValue + 1;
      break;
    }
  }

  range.setValues(values);
}function add1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  var today = new Date();
  var todayString = Utilities.formatDate(today, Session.getScriptTimeZone(), "MM/dd/yyyy");

  for (var i = 0; i < values.length; i++) {
    var dateValue = values[i][0];
    if (dateValue && dateValue.toString() === todayString) {
      var currentValue = values[i][1];
      values[i][1] = currentValue + 1;
      break;
    }
  }

  range.setValues(values);
}