logo
Tags down

shadow

Google Sheets - last row in every tab


By : Myat Thu
Date : October 14 2020, 02:15 PM
I wish did fix the issue. I was inquiring about this earlier this year and to all my months of research, it appears that this may not yet be a function available to script. I did speak to a friend of mine that works at google apps department and it seems this is not yet available.
I know this information was from 2016, however it is helpful to understand how google sheets works onOpen. Hope this helps.
code :


Share : facebook icon twitter icon

Google Sheets & CF7 Google Sheets Connector - Indirect Cell Reference Issue


By : gerardo martinez ala
Date : March 29 2020, 07:55 AM
hope this fix your issue I do not know if CF7 will have issue with this approach, but if it does not, I would use the formula below in row 1 of the column where you want the formula applied and then there is no need to apply a formula to each cell.
code :
=ARRAYFORMULA(IF(ROW(A1:A)=ROW(A1),"My Header",IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))))
IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0))
IF(ROW(A1:A)=ROW(A1),"My Header",IF(ISBLANK(X1:X),,If(X1:X>0,(65+25*Y1:Y),0)))

Google Script to email PDF from specific google sheet sends all sheets on some sheets, not others


By : user1979891
Date : March 29 2020, 07:55 AM
Does that help Reason of issue:
The reason of your issue is the following 2 lines.
code :
var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
+ (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
var sheetId = spreadsheet.getSheets()[sheetNumber].getSheetId();
+ sheetId
var sheetId = !isNaN(sheetNumber) && sheetNumber >= 0 && sheetNumber < spreadsheet.getSheets().length ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
+ (sheetId != null ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId))

Google Apps Script Auto Duplicate Sheets between conversion of Excel to Google Sheets


By : Junaid Rahman
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , The logic of the script is spot on. However, there are subtle nuisances in the function .getName() and Drive.Files.insertyou use that is causing the unintended behavior in the code.
folder.getName() gets the full name of the file, which includes the .xls/.xlsx extension. However, when you convert these files with Drive.Files.insert the extension is dropped. So your gsNames object has filenames without extension and but when the code tries to access the specific element using !gsNames[file.getName()] which has the file extension. It always returns undefined, which is evaluated as True.
code :
if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().replace(/\.[^/.]+$/, "")])
function convertCollection1() 
{
  var user = Session.getActiveUser(); // Used for ownership testing.1aJcbdGhwliTs_CZ-3ZUvQmGRDzBM7fv9
  var origin = DriveApp.getFolderById("1dPsDfoqMQLCokZK4RN0C0VRzaRATr9AN");
  var dest = DriveApp.getFolderById("1M6lDfc_xEkR4w61pUOG4P5AXmSGF1hGy");

  // Index the filenames of owned Google Sheets files as object keys (which are hashed).
  // This avoids needing to search and do multiple string comparisons.
  // It takes around 100-200 ms per iteration to advance the iterator, check if the file
  // should be cached, and insert the key-value pair. Depending on the magnitude of
  // the task, this may need to be done separately, and loaded from a storage device instead.
  // Note that there are quota limits on queries per second - 1000 per 100 sec:
  // If the sequence is too large and the loop too fast, Utilities.sleep() usage will be needed.
  var gsi = dest.getFilesByType(MimeType.GOOGLE_SHEETS), gsNames = {};
  while (gsi.hasNext())
  {
    var file = gsi.next();
    if(file.getOwner().getEmail() == user.getEmail())
      gsNames[file.getName()] = true;

    Logger.log(JSON.stringify(gsNames))
  }

  // Find and convert any unconverted .xls, .xlsx files in the given directories.
  var exceltypes = [MimeType.MICROSOFT_EXCEL, MimeType.MICROSOFT_EXCEL_LEGACY];
  for(var mt = 0; mt < exceltypes.length; ++mt)
  {
    var efi = origin.getFilesByType(exceltypes[mt]);
    while (efi.hasNext())
    {
      var file = efi.next();
      // Perform conversions only for owned files that don't have owned gs equivalents.
      // If an excel file does not have gs file with the same name, gsNames[ ... ] will be undefined, and !undefined -> true
      // If an excel file does have a gs file with the same name, gsNames[ ... ] will be true, and !true -> false
      if(file.getOwner().getEmail() == user.getEmail() && !gsNames[file.getName().replace(/\.[^/.]+$/, "")])
      {
        Drive.Files.insert (
          {title: file.getName(), parents: [{"id": dest.getId()}]},
          file.getBlob(),
          {convert: true}
        );
        // Do not convert any more spreadsheets with this same name.
        gsNames[file.getName()] = true;
      }
    }
  }
  Logger.log(JSON.stringify(gsNames))
}

Pull unique values from other sheets as new sheets keep getting added in Google Sheets


By : fzhengnyc
Date : March 29 2020, 07:55 AM
Hope this helps I'm trying to generate a list of unique items from column A of an indeterminate number of sheets. The idea is that over time, more sheets will be added and I don't want to have to update formulas to account for the new sheets. , you can use this script:
code :
function SNAME(option) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var thisSheet = sheet.getName(); 
if(option === 0){                  // ACTIVE SHEET NAME =SNAME(0)
return thisSheet;
}else if(option === 1){            // ALL SHEET NAMES   =SNAME(1)
var sheetList = [];
ss.getSheets().forEach(function(val){
   sheetList.push(val.getName())
});
return sheetList;
}else if(option === 2){            // SPREADSHEET NAME  =SNAME(2)
return ss.getName();

}else{
return "#N/A";                     // ERROR MESSAGE
};
};

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  
var src = sheet.getRange("A1");
var str = src.getValue(); 
var cell = sheet.getRange("A2"); 
cell.setFormula(str);
}
=ARRAYFORMULA("=ARRAYFORMULA(QUERY({"&TEXTJOIN("; ", 1, 
 FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!A2:B, "&
 FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!C2:C&"&
 FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!A1")&
 "}, ""select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3""))")

Error setting google sheets cell value based on criteria - Google Sheets Script


By : user3324621
Date : March 29 2020, 07:55 AM
it should still fix some issue I am trying to change a range of cells in Column 6 of an excel sheets page based on whether values in a similar range (but in column 3) are larger than its old value. In other words, I am trying to have a column contain all the highs of a constantly changing data set in column 3. , Try this:
code :
function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!="Sheet1")return;
  if (e.range.rowStart>1 && e.range.rowStart<8 && e.range.columnStart==3) {
    if (e.value > e.oldValue) {
      e.range.offset(0,3).setValue(e.value);
    }
  }
}
Related Posts Related Posts :
shadow
Privacy Policy - Terms - Contact Us © voile276.org