Google APPS Script Engineer

Promote your AIPRM Prompt
1

Link to Prompt

link to Prompt "Google APPS Script Engineer" by "Carles Menéndez" - AIPRM for ChatGPT

What the Prompt does

Specific Prompt for Google APPS Script dev.

The user will receive the code already made with the “functions that APPS Script likes”, and actual(2023) instructions to use it (With code.ai the process in this field are wrong, with Google APPS Script Engineer this will never happen).

Actually, there is no exact prompt that simplifies all the process to make an LLM learn about Apps Script.

This one actually has the

Example Prompt Output GPT3.5

Input:
We have two sheets, sheet one is called Customers, sheet 2 is called Duplicates. Your task is to generate a code that with a function called eliminateDuplicateRecords, column L of the Customers sheet is checked. The objective is to delete the records that match column A of the Duplicates Sheet. It implies that from the Customers sheet, we cannot delete any contact that is not in the Duplicates sheet. The review must be done from below to avoid the Out of bounce and that only the data from the cell that matches is eliminated, that is, it remains empty.

Output:
Created with AIPRM Prompt “Google APPS Script Engineer”

##Google APPS Script Engineer :smiley:

Here’s the code to accomplish the task you described:

function eliminateDuplicateRecords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var customersSheet = ss.getSheetByName("Customers");
  var duplicatesSheet = ss.getSheetByName("Duplicates");
  
  var customersData = customersSheet.getRange("L2:L").getValues();
  var duplicatesData = duplicatesSheet.getRange("A2:A").getValues();
  
  var deletedCount = 0;
  
  for (var i = customersData.length - 1; i >= 0; i--) {
    var customerRecord = customersData[i][0];
    
    for (var j = 0; j < duplicatesData.length; j++) {
      var duplicateRecord = duplicatesData[j][0];
      
      if (customerRecord === duplicateRecord) {
        customersSheet.getRange(i + 2, 12).clearContent(); // Column L is index 12
        deletedCount++;
        break;
      }
    }
  }
  
  Logger.log("Deleted " + deletedCount + " duplicate records.");
}

This code compares the values in column L of the “Customers” sheet with the values in column A of the “Duplicates” sheet. If a match is found, the corresponding cell in column L of the “Customers” sheet is cleared.

To use this code:

  1. Open your Google Sheets document.
  2. Go to “Extensions” → “Apps Script”.
  3. Replace any existing code with the code provided.
  4. Save the script.
  5. Close the Apps Script editor.
  6. In your Sheets document, you should now see a new custom menu named “Custom Menu”.
  7. Click on the “Custom Menu” and select “Eliminate Duplicate Records”.
  8. The code will run, and duplicate records will be deleted from column L of the “Customers” sheet.

Please try the code and let me know if it meets your requirements or if you need any further assistance.

Done.

Note: If you are not using English (UK) as your language preference, please let me know, and I’ll translate “Done” accordingly.

Example Prompt Output GPT4

Input:
We have two sheets, sheet one is called Customers, sheet 2 is called Duplicates. Your task is to generate a code that with a function called eliminateDuplicateRecords, column L of the Customers sheet is checked. The objective is to delete the records that match column A of the Duplicates Sheet. It implies that from the Customers sheet, we cannot delete any contact that is not in the Duplicates sheet. The review must be done from below to avoid the Out of bounce and that only the data from the cell that matches is eliminated, that is, it remains empty.

Output:
Google APPS Script Engineer :smiley:

To achieve the task of deleting records in the “Customers” sheet based on matches with column A in the “Duplicates” sheet, you can use the following code:

Javascript — Copy Code

function eliminateDuplicateRecords() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var customersSheet = ss.getSheetByName("Customers");
  var duplicatesSheet = ss.getSheetByName("Duplicates");
  
  var customersData = customersSheet.getDataRange().getValues();
  var duplicatesData = duplicatesSheet.getRange("A:A").getValues().flat();
  
  var customersLastRow = customersSheet.getLastRow();
  var columnL = customersSheet.getRange("L1:L" + customersLastRow);
  
  for (var i = customersData.length - 1; i >= 0; i--) {
    var customerValue = customersData[i][11]; // Column L is index 11 (zero-based)
    
    if (duplicatesData.includes(customerValue)) {
      columnL.getCell(i + 1, 1).setValue("");
    }
  }
}

Explanation:

  1. The eliminateDuplicateRecords function is defined to perform the required task.
  2. The active spreadsheet is obtained using getActiveSpreadsheet().
  3. The “Customers” and “Duplicates” sheets are accessed using getSheetByName().
  4. The data ranges in the sheets are fetched using getDataRange() and getRange().
  5. The values from column A in the “Duplicates” sheet are extracted using getValues() and flattened into a one-dimensional array using flat().
  6. The last row index of the “Customers” sheet is obtained using getLastRow().
  7. The range of values in column L of the “Customers” sheet is selected using getRange().
  8. The loop iterates over the rows of the “Customers” sheet data in reverse order.
  9. The value in column L (index 11) of each row is compared with the values in the “Duplicates” sheet.
  10. If a match is found, the corresponding cell in column L is set to an empty value using setValue("").

Please try running the eliminateDuplicateRecords function in your Google Sheets environment by following these steps:

  1. Open your Google Sheets document.
  2. Click on “Extensions” in the menu.
  3. Select “Apps Script” to open the Apps Script editor.
  4. Paste the provided code into the editor, replacing any existing code.
  5. Save the script and close the editor.
  6. You can now run the eliminateDuplicateRecords function by clicking on the custom menu item that will appear after reloading your spreadsheet.

Let me know if you encounter any issues or need further assistance.

How I came up with it

The prompt CODE.AI inspired me to do this better version specifically for Google APPS Script.

Since artificial intelligence came into play, I’ve been exploring and testing various methods to employ AI for computer-related tasks. I found it most interesting that AIPRM allows me to create my own code and apply it to my Google Sheets and Docs in order to improve results and reduce time.

¿What could be better? Well, that’s how I came up with this specific Prompt. The one that let me create my code dedicated for APPS Script.

As you can see in the results with only a sentence, the user will receive the code already made with the “functions that APPS Script likes”, actual instructions to use it (With code.ai the process in this field are wrong, with Google APPS Script Engineer this will never happen).

More insights

Also, I included different examples from APPS Script documentation, so it already knows the code that really works when you need to do, for example, traducing tasks.

1 Like
2

Please revise your prompt and entry to 100% ENGLISH

2 Likes