Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Put a google sheets formula in a cell from Apps Script

I'm usign this code:

function doPost(e) {

  var ss = SpreadsheetApp.openById('');
  var sh = ss.getSheetByName('Hoja 1');
  
  var data = Utilities.base64Decode(e.parameters.data);
  var blob = Utilities.newBlob(data, e.parameters.mimetype, e.parameters.filename);
  var fileId = DriveApp.getFolderById(e.parameters.folderId).createFile(blob).getId();
  var lastfila= ss.getLastRow()+1; 
  const itemStock='='+'G'+lastfila+'+'+'SUMAR.SI.CONJUNTO('+"'Hoja 2'!D:D;'Hoja 2'!C:C;B"+lastfila+")";
  var rowData = [];
  rowData.push(e.parameters.itemName[0]);
  rowData.push(e.parameters.itemDescription[0]);
  rowData.push(e.parameters.itemCategory[0]);
  rowData.push(e.parameters.filename[0]);
  rowData.push(fileId);
  rowData.push('https://drive.google.com/file/d/' + fileId);
  rowData.push(e.parameters.itemUnit[0]);
  rowData.push(e.parameters.itemLocation[0]);
  rowData.push(itemStock);
  sh.appendRow(rowData);
  
  

  return ContentService.createTextOutput('Image: ' + e.parameters.filename + ' with ID: ' + fileId + ' successfully uploaded to Google Drive' );  
  
}

for some reason when adding the row created from the code, the spreadsheet does not take the formula "SUM.SI.CONJUNTO()"(SUMIFS)

The problem

How can I solve it? Thanks.

Find a way to send spreadsheet form from Apps Script

Solution

  • From your script and your error message, how about changing SUMAR.SI.CONJUNTO to SUMIFS? When your script is modified, it becomes as follows.

    From:

    const itemStock='='+'G'+lastfila+'+'+'SUMAR.SI.CONJUNTO('+"'Hoja 2'!D:D;'Hoja 2'!C:C;B"+lastfila+")";

    To:

    const itemStock = '=' + 'G' + lastfila + '+' + 'SUMIFS(' + "'Hoja 2'!D:D;'Hoja 2'!C:C;B" + lastfila + ")";

    or

    const itemStock = `=G${lastfila}+SUMIFS('Hoja 2'!D:D;'Hoja 2'!C:C;B${lastfila})`;

    Note:

    • When your image of the error message, it seems Función desconocida: 'SIMIFS'.. I'm worried about misspellings. And also, in this case, I'm worried that you might not use SUMAR.SI.CONJUNTO. If you are using SIMIFS, please modify SUMIFS or SUMAR.SI.CONJUNTO, and test it again.

    • In this answer, it supposes that your other part of the script works. Please be careful about this.

    References: