Scripts, scripts, scripts!

Click the settings icon on the editor toolbar…

Screen Shot 2020-11-28 at 1.03.55 AM

And then choose the ‘Hide Details’ option.

Summary

I’m pretty sure @discobot taught me that one :wink:

2 Likes

Hi! To find out what I can do, say @discobot display help.

2 Likes

Thank you.

1 Like

Yes it could work pretty well. I am not sure if it also replaces special characters though.

2 Likes

If by special characters, you mean things like “%(^!~#@*” etc… no, it won’t touch any of that.

3 Likes

Here is another one that I use all the time as an alternative to ARRAYFORMULA.
The neat thing about this is that it works with any formulas, whereas ARRAYFORMULA has some limitations.

Again, I didn’t write this one myself, and I can’t recall where I got it from (probably Stackoverflow), so I can’t take any credit for it. But I’ve been using it for 3 or 4 years and it’s never failed me.

What it does and how it works is described in the comments.

Summary
/**
 * Copy Formulas Down
 * This script copies functions from a source row (usually the first data row in a spreadsheet) to
 * rows at the end of the sheet, where the colums for these formulas are still empty.
 * It checks row from the bottom upwards, copying formulas into the rows where there are no formulas yet,
 * and stops as soon as it finds a row which has a formula already.
 * When copying formulas, it leaves values in other cells unchanged.
 */

/**
 * Copy formulas from the a source row in a sheet to rows below
 * @param  {Sheet} sheet     	Google Spreadsheet sheet
 * @param  {int} sourceRow 		(optional) 1-based index of source row from which formulas are copied, default: 2
 */
function copyFormulasDown(sheet, sourceRow) {
    if (sourceRow === undefined) {
        sourceRow = 2;
    }
    var formulas = getFormulas_(sheet, sourceRow);
    if (formulas !== {}) {
        var rows = sheet.getDataRange().getFormulas();
        for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
            copyFormulas_(sheet, r, formulas);
        }
    }
}
/**
 * Copy formulas into row r in sheet
 * @param  {Sheet} sheet     	Google Spreadsheet sheet
 * @param  {int} r 				1-based index of row where formulas will be copied
 * @param  {array} formulas 	array of objects with column index and formula string
 */
function copyFormulas_(sheet, r, formulas) {
    for (var i = 0; i < formulas.length; i++) {
        sheet.getRange(r + 1, formulas[i].c + 1).setFormulaR1C1(formulas[i].formula);
    }
}
/**
 * Read formulas from the source row, creating an array of objects
 * Each objects contains the column index and formula string
 * @param  {Sheet} sheet     	Google Spreadsheet sheet
 * @param  {int} r				1-based index of source row
 * @return {array}				array of objects
 */
function getFormulas_(sheet, r) {
    var row = sheet.getRange(r, 1, 1, sheet.getLastColumn()).getFormulasR1C1()[0];
    var formulas = [];
    for (var c = 0; c < row.length; c++) {
        if (row[c] !== '') {
            formulas.push({
                c: c,
                formula: row[c]
            });
        }
    }
    return formulas;
}

Edit: one thing I should point out is that if you’re adding rows then you need to run this every time a new row (or rows) get added. But that’s easy enough with a trigger.

9 Likes

Hey @Drearystate,

Is there any script to send an email or two when a row is created?

I usually try to stay away from scripts but my Zapier keeps stoping and I’ve no idea why.

Thanks

1 Like

Yes , you can modify the email script I listed earlier, have it address the same column that has rows added and it will email. I set it up to email dynamically so what you can do is have the email addresses pull if you are needing more than 1. This can be done in either Glide or Google sheets. In Glide you can have it add the email addresses to a separate sheet via set column or add row. and The script just needs to be pointed in that direction to find the email addresses.

3 Likes

If you don’t want to hassle with manually importing excel files (.csv format), well then use a script to do the work for you.

function importCSVFromGoogleDrive() {
  var spreadsheet = SpreadsheetApp.getActive();
  var file = DriveApp.getFilesByName("APPOINTMENTS.csv").next();
  var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
  var sheet = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
     sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
   }

Just set it for a timer to get the file and convert it into your sheets. Keep in mind this is for any .csv file including excel .csv files.

7 Likes

Hey All. Looking for some help with App Script which moves values only.

Sometimes I get the error message (target sheet is out of range) even when I manually delete all of the blank rows in the target sheet and then add new ones.

More recently I get the error message "TypeError: Cannot read properly ‘getlastrow’ of null (line 5, file “NewDatabase”)

I had made a tab called ‘NewDatabase’ but then deleted it. I don’t know what else it could be referring to.

Once a row is sent it is marked "moved’ and skipped the following times.

function Database() {
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("DailyImport");
var StartRow = 3;
var RowRange = ActiveSheet.getLastRow() - StartRow + 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,15);
var AllValues = WholeRange.getValues();

//iterate loop
for (i in AllValues) {

//set current row
var CurrentRow = AllValues[i];

//define column to check if sent (column numbering starts from "0" not "1")
var RowSent = CurrentRow[14];
var New = CurrentRow[8];


//if row has been sent, then continue to next iteration
if (RowSent == "Moved" || New == "ADD NEW!") 
   continue;
   
//set the row to look at
var setRow = parseInt(i) + StartRow;

//mark row as "sent"
ActiveSheet.getRange(setRow, 15).setValue("Moved");

var CopySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database");
var Target = CopySheet.getRange(CopySheet.getLastRow()+1,1);
ActiveSheet.getRange(setRow, 1, 1, 13).copyTo(Target);
 
}
}

Any ideas on how to fix and or improve greatly appreciated.

Thanks in advance.

2 Likes

Is the sheet named properly?

I can confirm that ‘DailyImport’ and ‘Database’ are identical in both the script and workbook.

Hmmmm…SO I copied your code, made 2 sheets that matched the code. Filled them with random jargon and the code executed fine…no errors…and column 15 lists everything as moved except rows 1 & 2

1 Like

I know you don’t want to hear this but I think you should copy the names of the sheets from your code and paste them as the name of the sheet, you may have a unseen space somewhere.

2 Likes

Thank you for taking the time to help me and sharing screenshots! :+1:

I have a few workbooks running the script and it is working. Errors have come up as I am working in new copies. Maybe some user error on my part?

The error I’ve seen the most is the “target range” (I’ve been very diligent in checking the target sheet. When testing deleting all rows and then adding thousands more than the source sheet.)

Since I seem to be getting errors that I’m not always able to reproduce, I was wondering if there was a better overall approach to structuring this script.

Yes, there is a different way, whether its better or not is up to you. I would do a for loop. When I get home I will take a look and see if I can do any better than you have already done. Your script is clean and easy to understand, I perform those actions differently thats all.

Thanks again for taking a look.

I continue to be amazed at this community and the support here. I’ve managed to build my first app (Ever!) using the Glide Library and help from this forum.

Cheers!

2 Likes

That’s great to hear man. Congrats!

2 Likes

Did you get the kinks worked out?

getLastRow is a Sheet method, if it’s operating on ‘null’ that probably means that the following line failed to retrieve a valid sheet object:

Whenever I’m trying to debug misbehaving functions, I find that console.log() becomes one of my best friends.

3 Likes