Google Sheet Column Tick box validation

Complete newbie here, sorry if this sounds silly.

I have 3 columns and 60 rows of tick boxes, I have the tick boxes adding up at the bottom of the sheet.

Question is I do not want a user to tick more than the available ticks per column each column has a maximum amount of tick boxes available.

For example column, A max ticks allowed is 12, B max ticks allowed 7 and C Max 25.

How to run a different script on multiple Google Sheet tabs?

You have an installable onEdit() trigger referencing script name “sendEmails2”. There are three sheets that the script can apply to, but the script as written is sheet specific. When you edit duplicate the script and update attributes for the second sheet, you find that only the second script is running.

There are several reasons that only the second script executes:
1 – the onEdit trigger can only reference one script. Even if you had three scripts, one for sheet, onedit can only reference one sheet.
2 – the scripts each have the same name. Google sheets can only run one OR the other.
3 – the scripts do not daisy chain to each other.

The solution is:

  • develop a single script
  • include the names of the key sheets in the script (as variables – as in the answer, or even in an array accessible by the script).
  • include the subject and message values of each sheet as variables.
  • allow the script to compare the name of the edited sheet to the names of the key sheets and execute accordingly.

There are many ways in which the code for this answer might have been written. Please consider this as one solution to your question.


function sendEmails2() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // names of the three tabs
  var sheetname01 = "CAN WE R - HS";
  var sheetname02 = "CAN WE R - DRAWING";
  var sheetname03 = "CAN WE R - third";
 
  // assume there are many sheets and we want the trigger to 
  // execute if the edit is on one of three specific sheets   
 if ((sheet.getSheetName() === sheetname01) || (sheet.getSheetName() === sheetname02) || (sheet.getSheetName() === sheetname03)){
   // sent email
   // Logger.log("DEBUG: do something");
   
   // populate the subject and message fields
   if (sheet.getSheetName() === sheetname01){
     var message1 = 'HS Co 1 / HS co 2 / HS co 3';
     var subject1 = 'Sending emails from a Spreadsheet CAN WE R - HS';
   } else if (sheet.getSheetName() === sheetname02){
     var message1 = 'drawing Co 1 / drawing co 2';
     var subject1 = 'Sending emails from a Spreadsheet CAN WE R - drawing';
   } else if (sheet.getSheetName() === sheetname02){
     var message1 = 'stuff  1 / stuff 2';
     var subject1 = 'Sending emails from a Spreadsheet CAN WE R - third';
   } // end if set message and subjects
   
   // This constant is written in column E for rows for which an email
   // has been sent successfully.
   var EMAIL_SENT = 'EMAIL_SENT';
   
   // get the data
   var startRow = 3; // First row of data to process
   var numRows = 1000; // Number of rows to process
   var dataRange = sheet.getRange(startRow, 1, numRows, 10)
   // Fetch values for each row in the Range.
   var data = dataRange.getValues();
   for (var i = 0; i < data.length; ++i) {
     var row = data(i);
     var emailAddress = row(1); // Second column
     var message1 = 'HS Co 1 / HS co 2 / HS co 3';
     var emailSent = row(4); // Sixth column
     if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
       var subject1 = 'Sending emails from a Spreadsheet CAN WE R - HS';
       MailApp.sendEmail(emailAddress, subject1, message1);
       sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT)
       // Make sure the cell is updated right away in case the script is interrupted
       SpreadsheetApp.flush();
       
     } // end if email sent
   }// end loop through data
 }// end IF sheet condition  
  return;
}

On a tangent… onEdit scripts are often designed to take advantage of Event Objects. You did not explain the full details of the trigger (other than onEdit), but possibly Event Objects might be used to make the script more dynamic.

VBA Excel copy the element throughout a whole workbook when the sheet is hidden

Good afternoon,

I would like to copy the textbox throughout the whole workbook, but I want to exclude one sheet at once.
The original answer is here:

Copy the element throughout a whole workbook

but now, even if I include the unwanted sheet I am getting an error:
The item with the specified name wasn’t found.

with the debugger showing:

    Set s = ws1.Shapes("AsBuiltBox") 

enter image description here

My full code looks like this, but I switched off the lines responsible for hiding the sheet, which I don’t want because it didn’t work.

    Sub Asbuiltcopy()

    Dim Ws As Worksheet, ws1 As Worksheet, ws2 As Worksheet, s As Shape

 Set ws1 = Worksheets("Readme")      'sheet containing original textbox
 'Set ws2 = Worksheets("Readme")
 Set s = ws1.Shapes("AsBuiltBox")     'name of original textbox

 'ws2.Hidden = True

 Application.ScreenUpdating = False

 For Each Ws In Worksheets
    'Ws.Unprotect "Secret"
   If Ws.Name <> ws1.Name Then
    s.Copy
    Ws.Paste
    Ws.Shapes(Ws.Shapes.Count).Top = s.Top
    Ws.Shapes(Ws.Shapes.Count).Left = s.Left
 End If
 Next Ws

 Application.ScreenUpdating = True

 Call AsReadmeremove

 'ws2.Hidden = False

 End Sub

I also tried to solve this problem here:

Copy the element throughout the whole workbook with exclusion some sheets

Is there any way to make it running?

I need to copy Data from Google Sheet 1 to a Row in Google Sheet2

I am very new to this whole scripting business and I am looking for some advice to solve my problem.
I try to copy data from a column range (H3:H10) in Sheet 1 (Search) to the first free row in sheet 2 (Data). I found this piece of code (from Kos, written 2017), which I thought would do the trick. Unfortunately I can’t figure out how to change the code, so it would write the data into the first free row instead of writing the whole data in 1 column in the first free row.

Anyone out here who can help me?

function copyPaste() 
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var copySheet = ss.getSheetByName("Search");
var pasteSheet = ss.getSheetByName("Data");
// get source range
var source = copySheet.getRange(3,8,8,1);
// get destination range
var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,1,1,1);
// copy values to destination range
source.copyTo(destination);
// clear source values
source.clearContent();
}

enter image description here

synchronization – Howto sync offline sheet from Sheets app

I did some offline work on a google sheet file on my mobile using Sheets app. I actually did not realize that I am offline, as I never set this option, at least not on purpose.

After I connected to the file using laptop browser, and did some extra work. only than I realized I do not see the changes I made on the mobile app. Reopen the app, and the changes were lost as the file was synced with the last edit on the laptop, which did not have these edits.

So two questions:

Is there some sign to indicates “file is offline”?

how to force sync from mobile app, after working offline?

google spreadsheets – Howto sync offline sheet from Sheets app

I did some offline work on a google sheet file on my mobile using Sheets app.
I actually did not realize that I am offline, as I never set this option, at least not on purpose.

After I connected to the file using laptop browser, and did some extra work.
only than I realized I do not see the changes I made on the mobile app.
Reopen the app, and the changes were lost as the file was synced with the last edit on the laptop, which did not have these edits.

So two questions:

  1. Is there some sign to indicates “file is offline”?

  2. how to force sync from mobile app, after working offline?

Trying to fetch data from www.csgoempire.com with ImportXML function in google sheet

I am trying to fetch data from below link using google sheet’s importxml function:

https://csgoempire.com/history?seed=1997

This is a betting site, and the link displays a list of roulette roll IDs on 26 June 2020. I am trying to fetch that data in the following sample format:

  1. #5317018 – 3
  2. #5317019 – 12
  3. #5317020 – 14
  4. …..

I used the following command, with no result:
=importxml(“https://csgoempire.com/history?seed=1997″,”/html/body/div(1)/div(1)/div(3)/div/div/div(1)/div/div”)

Can you help me, please?

Thank you!